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).

				1st Jul 2017	1st Jan 2018	1st Jul 2018	1st Jan 2019	1st Jul 2019	1st Jan 2020
From	To	No. of Days		31st Dec. 2017	30th Jun 2018	31st Dec. 2018	30th Jun 2019	31st Dec. 2019	30th Jun 2020
1st Jul 2017	30th July 2018	396		No. of days between above dates ???

<tbody>
</tbody>

 

[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