Calculate the number of days of a date range occur between two dates

Question

I have a datasheet that contains a list of start and end dates for a task. I need to calculate how many days between the start date and end date are part of our Christmas break (11th December to 7th January)

So for example,when start date is 10/12/2012 and end date is 12/01/2013, 28 of the days are between those dates. when the start date is 15/12/2012 and the end date is 12/03/2013, then 22 days of days are between those dates. If the start date is 10/12/2012 and the end date is 12/01/2014, 56 of the days are between those dates (because there's two years of the range).

I need to do this with a formula because of the requirements that I've been set. Initially I decided to use the number of times Christmas Day (25th December) occurs and just calculate 4 weeks per occurrence.

The formula I used was

=FLOOR((E12-A25)/365,1)+IF(OR(MONTH(E12)=12,
       MONTH(A25)=12),
       IF(AND(DAY(A25)<=25,DAY(E12)>=25),1,0),
       IF(OR(MONTH(A25)>=12,
       IF(MONTH(E12)<MONTH(A25),
          MONTH(E12)+12,
          MONTH(E12))>=12),1,0))*28

But obviously this doesn't help if the range start and end date falls between those two dates.

Any suggestions? I'm at a dead end

what is the problem ? is getting 56 days between 10/12/2012 and 12/01/2014 a problem? — Bhavik Shah
No, that's the correct answer to get. The issue is that the current formula doesn't calculate it properly. The formula only checks to see if 25th December happens between two dates. If the range was 11/12/2012 to 23/12/2012 it would return 0. It should return 12. — Nick Perkins

richardtallent richardtallent · Accepted Answer · 2012-12-19T07:18:28

Your date math on the second example is wrong -- there are 24 days in that range, not 22.

I can get you there for one holiday period:

LET:

A1 contain the holiday start (11-Dec-2012)
A2 contain the holiday end (7-Jan-2013)
B1 contain the start date
B2 contain the end date

FORMULA:

=MAX(MIN(A2+1,B2+1),A1) - MIN(MAX(A1,B1),A2+1)

The formula basically finds the overlapping date range, if there is one, and subtracts to get the number of whole days. The "+1" is because your "end dates" are actually inclusive, so for date math you need to have the holiday ending on 8 Jan, not 7 Jan, to capture that last day.

But this only works for a single year's holiday. You could store holiday ranges in other cells and use the same formula and add them all up, but you'll be limited to however many years you set up.

Calculate the number of days of a date range occur between two dates

2 Answers