Counting a number of days from a range of date that is between another range of date

[shane22]

Hi there,
I am having quite some hard time to figure a formula for the purpose of counting the number of days from a range of date that is between another range of date.

e.g.. Check in Date: 20/09/2015 and Check out Date: 29/09/2015.

I have Low Season that is between 10/09/2015 to 25/09/2015
I have High Season that is between 26/09/2015 to 10/10/2015

I want to know how many days of booking is included in the Low Season and how many days is included in the High Season

Which formula should be used for this kind of lookup?

I would greatly appreciate some guidance !

Thanking you in advance

Shane

 

[Nunes]

Hi there!

In your yellow input cell, use this formula (or check the attached file):

=IFERROR(ROWS(INDEX($A:$A,B10):INDEX($A:$A,C10) INDEX($A:$A,F14):INDEX($A:$A,G14)),0)+IFERROR(ROWS(INDEX($A:$A,B10):INDEX($A:$A,C10) INDEX($A:$A,F15):INDEX($A:$A,G15)),0)+IFERROR(ROWS(INDEX($A:$A,B10):INDEX($A:$A,C10) INDEX($A:$A,F16):INDEX($A:$A,G16)),0)

This use of ROWS() I learned it yesterday from another thread here in the fórum, from @Luke M
Here:
http://chandoo.org/forum/threads/recognising-no-of-days-within-years.24987/

I hope this helps you out.

Best

 

[shane22]

wow ! Nunes Thank you so much! I checked also this reply from Luke and was trying to understand it but couldn't make it work as I didn't quite get how the INDEX work yet, but still looking into it!
But great thanks for the help!

 

[NARAYANK991]

Hi ,

See if this works :

=(MIN(Low_Season_To,Date_of_Departure) - MAX(Low_Season_From,Date_of_Arrival) + 1) * ((Low_Season_To>=Date_of_Arrival)*(Low_Season_From<=Date_of_Departure))

A similar formula can be used to derive the count of overlapping days for the High Season.

Narayan

 

[shane22]

Hi There Narayan,

Thank you also for helping out, I'll definitely try this also I'll keep you posted.

Thanks again for helping out!