ShowAll Questionssorted byDate Posted
Config Migration

# # of possible Business Days during current month for a user who started after days 1

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)
William Tran
Hi,