+ Start a Discussion
Adam CoppinAdam Coppin 

Formula to calculate the number of a weekday between two dates

Hi All,

I'm a bit new to SalesForce and am trying to learn the formula syntaxes through example, but am stuck on a particular problem.  I need to calculate the number of a given week day between two dates.  For example, the number of Mondays between a start date and an end date for a record. or the number of tuesdays.

Basically, I have an object that captures the times that the object occurs on a given day of the week through a text field for each day of the week.  I need to calculate the number of times the days with entries in them occur between the start date and end date of a record.

For example, record 1 has an entry for Monday, Wednesday, and Thursday.
Record 2 has an entry for Monday, Tuesday, Thursday, and Friday.

How can I count the number of occurrences between the start date and end date fields?

Thanks!

Adam
Best Answer chosen by Adam Coppin
Adam CoppinAdam Coppin
Found a solution after some digging and poking at the examples given for calculating the number of work days between two dates.  During my poking, I realized that the reason I was having trouble editing this particular formula was because nowhere I saw it, was it broken down and explained.  Hopefully, this will be helpful to a few others looking for ways to count the number of mondays between two dates, or any other particular day of the week...

First off, here's the formula to calculate the number of Mondays between two dates (Start_Date__c and End_Date__c):
CASE(MOD( Start_Date__c  - DATE(1985,6,24),7), 
  0 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,0,2,0,3,0,4,0,5,0,6,0,0),
  1 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,0,2,0,3,0,4,0,5,0,6,1,0), 
  2 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,0,2,0,3,0,4,0,5,1,6,1,0), 
  3 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,0,2,0,3,0,4,1,5,1,6,1,0), 
  4 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,0,2,0,3,1,4,1,5,1,6,1,0), 
  5 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,0,2,1,3,1,4,1,5,1,6,1,0), 
  6 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,1,2,1,3,1,4,1,5,1,6,1,0), 
  999) 
  + 
  (FLOOR(( End_Date__c - Start_Date__c )/7))

The formula could be simplified to save a few bytes, but that would make it that much harder to understand and the extra characters make for much easier reading.  But without explanation, this is a bit convoluted to work through and modify, so here goes!

The first line:
CASE(MOD( Start_Date__c  - DATE(1985,6,24),7),

Uses a reference Monday some time in the past (in this case June 24th 1985) and calculates the integer remainder when you divide the start date from the reference date (that's the MOD function - you can look it up!).  Basically, this is the number of days of the week your start date is offset from the reference date.  So if your start date is a Monday, the MOD portion will return 0.  If your start date is a Wednesday, it will return 2 and so on.  The CASE bit really comes into play in the next several lines, but it is essentially saying that based on the evalution of the MOD statement, then return a particular value.

Next comes 7 lines, one for each day of the week, that accounts for all of the possible offsets between your start date and your reference date.  When the offset between your start date and your reference date is zero, then they're on the same day of the week.  Count forward from there to get the offset for any other day of the week combinations.  Each of these lines (when written out like we have above) therefore references a day of the week.

Plucking one of the lines out, let's use Wednesday for this example or offset 2, we'll walk through the next level of CASE statement:
2 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,0,2,0,3,0,4,0,5,1,6,1,0),

So basically, we're saying that the start date is a Wednesday (2 offset from Monday) and for the value we want to return here, we need to determine how many days would have to pass before we passed our reference day.  Since this line represents Wednesday in our example, we know that 5 days would have to pass before you had a Monday (Thursday, Friday, Saturday, Sunday, Monday).  So, for each possible start date to end date offset, we either return 0 for anything that hasn't passed a Monday, or 1 for when one has been passed.  Hence, in the case list when a 4 offset is returned we don't add anything, but when 5 or 6 is returned, we know we've passed a Monday and we add 1.

This whole first section is accounting for incomplete weeks... if we didn't and you were counting wednesdays (for instance) then your count could be off by 1 or 2.

The 999 line is there as an error check and because a CASE statement requires a default value if no others are returned.  Since we're calculating based on days of the week, the only time we should see something with 999 is if there's an error in the calculations somewhere.

The last bit counts the whole weeks, think of it as the bulk number counter.
(FLOOR(( End_Date__c - Start_Date__c )/7))

This just returns the number of weeks between the start date and end date, rounded down (that's the FLOOR function).  Rounding down is the right approach here as the first section handles the incomplete week calculations!

So this is all well and good for when you're calculating Mondays, but how do I calculate another day of the week?

Thursday's you say?

Well - read on!

The changes are pretty easy, but you do have to watch your commas.  Understanding what each line is doing will help with this immensely.

first off, change your reference date to a day of the week way in the past (you don't want to hazard the chance that you'll hit it as you're calculations will go wonky.  Lots of examples out there use 1900 - use the calendar on your computer to find a nice early one.
 
CASE(MOD( Start_Date__c  - DATE(1985,6,24),7),

Then, and this is the complicated part... that's it.  Really - nothing else to do here... move along.

Hope this helps someone - would have been really helpful to me this morning if this had actually been broken down!

All Answers

Shikha AgashiShikha Agashi
You have to write a trigger for this scenario. You should write trigger on before insert, before update and after delete.
Adam CoppinAdam Coppin
Found a solution after some digging and poking at the examples given for calculating the number of work days between two dates.  During my poking, I realized that the reason I was having trouble editing this particular formula was because nowhere I saw it, was it broken down and explained.  Hopefully, this will be helpful to a few others looking for ways to count the number of mondays between two dates, or any other particular day of the week...

First off, here's the formula to calculate the number of Mondays between two dates (Start_Date__c and End_Date__c):
CASE(MOD( Start_Date__c  - DATE(1985,6,24),7), 
  0 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,0,2,0,3,0,4,0,5,0,6,0,0),
  1 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,0,2,0,3,0,4,0,5,0,6,1,0), 
  2 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,0,2,0,3,0,4,0,5,1,6,1,0), 
  3 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,0,2,0,3,0,4,1,5,1,6,1,0), 
  4 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,0,2,0,3,1,4,1,5,1,6,1,0), 
  5 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,0,2,1,3,1,4,1,5,1,6,1,0), 
  6 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,1,2,1,3,1,4,1,5,1,6,1,0), 
  999) 
  + 
  (FLOOR(( End_Date__c - Start_Date__c )/7))

The formula could be simplified to save a few bytes, but that would make it that much harder to understand and the extra characters make for much easier reading.  But without explanation, this is a bit convoluted to work through and modify, so here goes!

The first line:
CASE(MOD( Start_Date__c  - DATE(1985,6,24),7),

Uses a reference Monday some time in the past (in this case June 24th 1985) and calculates the integer remainder when you divide the start date from the reference date (that's the MOD function - you can look it up!).  Basically, this is the number of days of the week your start date is offset from the reference date.  So if your start date is a Monday, the MOD portion will return 0.  If your start date is a Wednesday, it will return 2 and so on.  The CASE bit really comes into play in the next several lines, but it is essentially saying that based on the evalution of the MOD statement, then return a particular value.

Next comes 7 lines, one for each day of the week, that accounts for all of the possible offsets between your start date and your reference date.  When the offset between your start date and your reference date is zero, then they're on the same day of the week.  Count forward from there to get the offset for any other day of the week combinations.  Each of these lines (when written out like we have above) therefore references a day of the week.

Plucking one of the lines out, let's use Wednesday for this example or offset 2, we'll walk through the next level of CASE statement:
2 , CASE( MOD( End_Date__c - Start_Date__c ,7),1,0,2,0,3,0,4,0,5,1,6,1,0),

So basically, we're saying that the start date is a Wednesday (2 offset from Monday) and for the value we want to return here, we need to determine how many days would have to pass before we passed our reference day.  Since this line represents Wednesday in our example, we know that 5 days would have to pass before you had a Monday (Thursday, Friday, Saturday, Sunday, Monday).  So, for each possible start date to end date offset, we either return 0 for anything that hasn't passed a Monday, or 1 for when one has been passed.  Hence, in the case list when a 4 offset is returned we don't add anything, but when 5 or 6 is returned, we know we've passed a Monday and we add 1.

This whole first section is accounting for incomplete weeks... if we didn't and you were counting wednesdays (for instance) then your count could be off by 1 or 2.

The 999 line is there as an error check and because a CASE statement requires a default value if no others are returned.  Since we're calculating based on days of the week, the only time we should see something with 999 is if there's an error in the calculations somewhere.

The last bit counts the whole weeks, think of it as the bulk number counter.
(FLOOR(( End_Date__c - Start_Date__c )/7))

This just returns the number of weeks between the start date and end date, rounded down (that's the FLOOR function).  Rounding down is the right approach here as the first section handles the incomplete week calculations!

So this is all well and good for when you're calculating Mondays, but how do I calculate another day of the week?

Thursday's you say?

Well - read on!

The changes are pretty easy, but you do have to watch your commas.  Understanding what each line is doing will help with this immensely.

first off, change your reference date to a day of the week way in the past (you don't want to hazard the chance that you'll hit it as you're calculations will go wonky.  Lots of examples out there use 1900 - use the calendar on your computer to find a nice early one.
 
CASE(MOD( Start_Date__c  - DATE(1985,6,24),7),

Then, and this is the complicated part... that's it.  Really - nothing else to do here... move along.

Hope this helps someone - would have been really helpful to me this morning if this had actually been broken down!
This was selected as the best answer
Adam CoppinAdam Coppin
Thanks Shikha, but it does seem possible with the formulas!  Please see my response to myself!
Nida Khan 5Nida Khan 5
Hello @Adam Coppin,

Well explained. Thanks.

Regards,
Nida
Anmol MirakhurAnmol Mirakhur
Hi Adam, 

I have a similar issue where I need to calculate the number of Mondays between two dates. One is a date stamp from a prior date, while another date is today's date. How would the formula work? Please explain as soon as you can. 

Thank you,

Anmol