+ Start a Discussion
Config MigrationConfig 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 TranWilliam Tran
Hi,

This link may help.
https://help.salesforce.com/apex/HTViewSolution?id=000001100&language=en_US

thx
SteveMoSteveMo
You might be able to tweak this formula a little to return a Number instead of a Percent.  At the end of the thread I modify it to only count Business Days => https://success.salesforce.com/answers?id=90630000000h3GXAAY