• Config Migration
  • NEWBIE
  • 0 Points
  • Member since 2015

  • Chatter
    Feed
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 1
    Questions
  • 0
    Replies
I have a field that returns the number of business days in the current month as of today.
I have date fields on each user for User Start Date and User End Date.
I want to be able to calculate the number of business days for the user and take into consideration the possibility that she started sometime after the first day of the month, or ended before the last day of the month.

Here is my current formula that does not consider the start or end dates. I have a -1 in the formula because our time tracking runs every day at 5 AM for all days up to the prior one (not current day)

CASE(MOD( DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),1) - DATE(1985,6,24),7),
0 , CASE( MOD( (TODAY()-1) - DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),1) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( (TODAY()-1) - DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),1) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( (TODAY()-1) - DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),1) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( (TODAY()-1) - DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),1) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( (TODAY()-1) - DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),1) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( (TODAY()-1) - DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),1) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( (TODAY()-1) - DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),1) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR(( (TODAY()-1) - DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),1) )/7)*5)