Counting days with multiple split ranges

[ahmedhussain]

Hi to all professionals
Need your professional help to put the below query in to excel formula please.

Basically i have two dates on the left of the table, which is input from user, i need to calculate the number of days between period split mentioned on the right of the below table.

Example: if input date is Jul 1st 2017 to Jul 31st 2018 on the left, total days are 396, so the required result is that there are 184 days from 01/07/2017 to 31/12/2017 and 181 days from 01/01/2018 to 30/06/2018 and 31 days from 01/07/2018 to 31/07/2018. Likewise, if the range gets bigger need to add the same on the right (period split).


[TABLE="width: 800, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1st Jul 2017[/TD]
[TD]1st Jan 2018[/TD]
[TD]1st Jul 2018[/TD]
[TD]1st Jan 2019[/TD]
[TD]1st Jul 2019[/TD]
[TD]1st Jan 2020[/TD]
[/TR]
[TR]
[TD]From[/TD]
[TD]To[/TD]
[TD]No. of Days[/TD]
[TD][/TD]
[TD]31st Dec. 2017[/TD]
[TD]30th Jun 2018[/TD]
[TD]31st Dec. 2018[/TD]
[TD]30th Jun 2019[/TD]
[TD]31st Dec. 2019[/TD]
[TD]30th Jun 2020[/TD]
[/TR]
[TR]
[TD]1st Jul 2017[/TD]
[TD]30th July 2018[/TD]
[TD]396[/TD]
[TD]
[/TD]
[TD]No. of days
between above
dates ???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Tetra201]

Assuming your dates are custom-formatted numbers (not text strings), try this:

=MAX(0,MIN(E2,$B$3)-MAX(E1,$A$3)+1)

 

[ahmedhussain]

You rock man !!!!
millions of thanks !!! this was magic