Days between (excluding Sundays)

Friday 11 January 2013 @ 5:09 am

I recently needed a formula to calculate the number of days in a date range, while excluding all of the Sundays in that range. My plan was to calculate all the dates in the range, then calculate the number of Sundays in the same range, and use the difference. To get all the dates I would use:

{@EndDate} - {@StartDate}

Depending on your environment, you may need to add one day to the result. In an environment that involves overnight stays (hospital, hotel, etc) anyone who checks in one day and checks out the next day would only be counted as one day, so the formula above would be correct. But if you are talking about most other things that start on one day and end on another day (attending a class, dates of coverage, etc) you will have to add one day to the result like this:

{@EndDate} - {@StartDate} + 1

To calculate the number of Sundays (or any day of the week) you can use one of these examples:

DateDiff ("ww",{@StartDate}-1, {@EndDate}, 1)

DateDiff ("ww",{@StartDate}-1, {@EndDate}, CrSunday)

Notice the “-1” in the middle of the formula. I have found that if the End Date happens to fall on a Sunday it will still be counted. But if the Start Date is on a Sunday then it will NOT be counted. So without that “-1” if you asked it how many Sundays there were in an 8 day period starting on a Sunday, it would only find one. If you want it to count both the beginning and ending Sunday you need to subtract one from the Start Date.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server