You need to sign in to do that
Don't have an account?
Katie Dunne
Formula to calculate the number of a specific work day between 2 dates
I'm looking for a formula to determine the number of a specific day (i.e Monday, Tuesday, Wednesday between 2 dates (this will be the current month). Essentially I'd like a formula that tells me that this month there were:
4 Mondays
5 Tuesdays
5 Wednesdays
4 Thursdays
4 Fridays
Is this possible?
4 Mondays
5 Tuesdays
5 Wednesdays
4 Thursdays
4 Fridays
Is this possible?
You can refer this solution mentioned in this example to start with your requirement :-
https://developer.salesforce.com/forums/?id=906F0000000MID6IAO
If this helps, kindly mark it as the best answer.
Thanks
Priya Ranjan
I have tried using that formula however it doesn't seem to work when the day is the first day of the month.
For example, when I run this formula to see how many Tuesdays were in this month, I get 4, when I know there were 5 this month - can anyone help me please?!:
IF( Session_Day__c ="Tuesday",
(
CASE(MOD( (DATE(YEAR(TODAY()), MONTH(TODAY()),1)) - DATE(1985,6,25),7),
0 , CASE( MOD( (DATE(YEAR(TODAY()),MONTH(TODAY()) + 1,1) - 1) - (DATE(YEAR(TODAY()), MONTH(TODAY()),1)) ,7),1,0,2,0,3,0,4,0,5,0,6,0,0),
1 , CASE( MOD( (DATE(YEAR(TODAY()),MONTH(TODAY()) + 1,1) - 1) - (DATE(YEAR(TODAY()), MONTH(TODAY()),1)) ,7),1,0,2,0,3,0,4,0,5,0,6,1,0),
2 , CASE( MOD( (DATE(YEAR(TODAY()),MONTH(TODAY()) + 1,1) - 1) - (DATE(YEAR(TODAY()), MONTH(TODAY()),1)) ,7),1,0,2,0,3,0,4,0,5,1,6,1,0),
3 , CASE( MOD( (DATE(YEAR(TODAY()),MONTH(TODAY()) + 1,1) - 1) - (DATE(YEAR(TODAY()), MONTH(TODAY()),1)) ,7),1,0,2,0,3,0,4,1,5,1,6,1,0),
4 , CASE( MOD( (DATE(YEAR(TODAY()),MONTH(TODAY()) + 1,1) - 1) - (DATE(YEAR(TODAY()), MONTH(TODAY()),1)) ,7),1,0,2,0,3,1,4,1,5,1,6,1,0),
5 , CASE( MOD( (DATE(YEAR(TODAY()),MONTH(TODAY()) + 1,1) - 1) - (DATE(YEAR(TODAY()), MONTH(TODAY()),1)) ,7),1,0,2,1,3,1,4,1,5,1,6,1,0),
6 , CASE( MOD( (DATE(YEAR(TODAY()),MONTH(TODAY()) + 1,1) - 1) - (DATE(YEAR(TODAY()), MONTH(TODAY()),1)) ,7),1,1,2,1,3,1,4,1,5,1,6,1,0),
999)
+
(FLOOR(( (DATE(YEAR(TODAY()),MONTH(TODAY()) + 1,1) - 1) - (DATE(YEAR(TODAY()), MONTH(TODAY()),1)) )/7))), 0)