calculate number of days within one date range that fall within a second date range

[mrcann]

Hello there!

I want to calculate how many of the days within one date range fall within a second date range.

For example (in dd/mm/yyyy format):

Date range 1:
A1: 15/12/2010 to
B1: 17/02/2011

Date range 2:
C1: 01/01/11 to
D1: 31/03/11

The question I want to answer is "how many of the days between 15 December 2010 and 17 February 2011 fall between 1 January 2011 and 31 March 2011 inclusive?"

Many thanks

 

[T. Valko]

Hello there!

I want to calculate how many of the days within one date range fall within a second date range.

For example (in dd/mm/yyyy format):

Date range 1:
A1: 15/12/2010 to
B1: 17/02/2011

Date range 2:
C1: 01/01/11 to
D1: 31/03/11

The question I want to answer is "how many of the days between 15 December 2010 and 17 February 2011 fall between 1 January 2011 and 31 March 2011 inclusive?"

Many thanks

Try this...

=MAX(0,MIN(B1,D1)-MAX(A1,C1)+1)

 

[mrcann]

Thanks T.Valko, but that unfortunately does not work. It just returns zeros.

 

[T. Valko]

Thanks T.Valko, but that unfortunately does not work. It just returns zeros.

Works just fine for me.

Are you sure your dates are true Excel dates or are they TEXT strings that look like dates?

What result do you get with this formula?

=COUNT(A1:D1)

If you get a number that is less than 4 then your dates aren't true Excel dates.

 

[mrcann]

Strange.

=COUNT (A1:D1) returns 4

Yet your formula returns zero in my workbook.

When I enter =MIN(0,MIN(C4,D4)-MAX(A4,B4)+1) I get -46, which is close to the right number, but in negative.

 

[T. Valko]

Strange.

=COUNT (A1:D1) returns 4

Yet your formula returns zero in my workbook.

When I enter =MIN(0,MIN(C4,D4)-MAX(A4,B4)+1) I get -46, which is close to the right number, but in negative.

I think you have the cell references in the wrong places...

Here's the formula in "pseudo-code":

=MAX(0,MIN(end date period 1, end date period 2)-MAX(start date period 1, start date period 2)+1)

 

[mrcann]

Really appreciate your help, but ended up using this formula:

=IF(AND(E4<H4, E4<I4), 0, IF(AND(D4>I4, E4>I4), 0, IF(AND(D4<=H4, E4<=I4, E4>=H4), E4-D4-(H4-D4), IF(AND(D4>=H4, E4<=I4), E4-D4, IF(AND(D4>=H4,E4>=I4, D4<=I4),E4-D4-(E4-I4),IF(AND(D4<=H4, E4>=I4), I4-H4, IF(AND(D4=H4, E4=I4), E4-D4, "false")))))))

where:
column D = start date of billing period
column E = end date of the billing period
column H = start date of the period I wanted to query
column I = end date of the period I wanted to query

I know it's not the most elegant solution, but it does the job.

Cheers

 

[T. Valko]

Really appreciate your help, but ended up using this formula:

=IF(AND(E4<H4, IF(AND(D4 0, E4<I4),>I4, E4>I4), 0, IF(AND(D4<=H4, E4<=I4, E4>=H4), E4-D4-(H4-D4), IF(AND(D4>=H4, E4<=I4), E4-D4, IF(AND(D4>=H4,E4>=I4, D4<=I4),E4-D4-(E4-I4),IF(AND(D4<=H4, E4>=I4), I4-H4, IF(AND(D4=H4, E4=I4), E4-D4, "false")))))))

where:
column D = start date of billing period
column E = end date of the billing period
column H = start date of the period I wanted to query
column I = end date of the period I wanted to query

I know it's not the most elegant solution, but it does the job.

Cheers

Here's a small sample file that demonstrates this.

zzzmrcann.xls 15kb

http://cjoint.com/?AEnoUnrnEgi

 

[PHIL.Pearce84]

Try this...

=MAX(0,MIN(B1,D1)-MAX(A1,C1)+1)

Hi There,

Wonder if you can help me, the above formula worked great, but is there a way to extend the range i.e B1 and A1 would be entire columns on a separate tab where all my holiday dates are held for multiple individuals within a team.

Ideally it would count the number of days all members of a particular team have holiday days between the dates in D1 and C1 in the above formula, I tried just create a column range but it didnt work.

Any help would be greatly appreciated.

Thanks