How to Find specific date in X number of days from within multiple date range?

[BlackBirdSR71]

Hi,

I want to calculate a specific date after x number of days from within following date-range only (table given below)!

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Trip#[/TD]
[TD]FROM[/TD]
[TD]TO[/TD]
[TD]DAYS[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12-MAR-2015[/TD]
[TD]24-AUG-2015[/TD]
[TD]165[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20-AUG-2015[/TD]
[TD]15-FEB-2016[/TD]
[TD]179[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]24-MAR-2016[/TD]
[TD]17-AUG-2016[/TD]
[TD]146[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]20-MAR-2017[/TD]
[TD]22-JUN-2017[/TD]
[TD]94[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]19-MAY-2018[/TD]
[TD]04-OCT-2018[/TD]
[TD]138[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]01-MAY-2019[/TD]
[TD]12-DEC-2019[/TD]
[TD]225[/TD]
[/TR]
</tbody>[/TABLE]
STARTING DATE : 12-MAR-2015
5 YEAR PERIOD : 11-MAR-2020

To elaborate further, I want to find an exact date when total trip's days reach 365th day mark and 730th day mark from within these selected dates only. "FROM" date is counted, but "TO" date is excluded from "DAYS" count.

e.g. according to above table, 365th day would be 14th April 2016 ..... and 730th day would be 9th May 2019

Thanks in advance!

 

[Peter_SSs]

Welcome to the MrExcel board!

This, using a helper column, produces the results you expect. However, I just want to check if your sample data is correct and/or your expected result. What makes me ask is that four days (20-AUG-2015 to 23-AUG-2015) are counted twice, once in Trip 1 and again in Trip 2. Is that correct?

Both formulas copied down.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Trip#	FROM	TO	DAYS
2	1	12-Mar-15	24-Aug-15	165	1		365	14/04/2016
3	2	20-Aug-15	15-Feb-16	179	166		730	9/05/2019
4	3	24-Mar-16	17-Aug-16	146	345
5	4	20-Mar-17	22-Jun-17	94	491
6	5	19-May-18	4-Oct-18	138	585
7	6	1-May-19	12-Dec-19	225	723
Dates

 

[BlackBirdSR71]

Hi Peter_SSs,

Thanks for your reply.

Sorry. I made an error in Trip 1 and Trip 2. Following is correct.

Dates

	A	B	C	D	E	F	G	H

<colgroup><col style="width: 30px;"><col style="width: 47px;"><col style="width: 80px;"><col style="width: 78px;"><col style="width: 55px;"><col style="width: 40px;"><col style="width: 21px;"><col style="width: 40px;"><col style="width: 83px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="align: right"]Trip#[/TD]
[TD="align: right"]FROM[/TD]
[TD="align: right"]TO[/TD]
[TD="align: right"]DAYS[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12-Mar-15[/TD]
[TD="align: right"]20-Aug-15[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]24-Aug-15[/TD]
[TD="align: right"]15-Feb-16[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]24-Mar-16[/TD]
[TD="align: right"]17-Aug-16[/TD]
[TD="align: right"]146[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20-Mar-17[/TD]
[TD="align: right"]22-Jun-17[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]19-May-18[/TD]
[TD="align: right"]4-Oct-18[/TD]
[TD="align: right"]138[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1-May-19[/TD]
[TD="align: right"]12-Dec-19[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"][/TD]

</tbody>

Thanks for the Formulas. Many Many Thanks for your help. It worked!

If I may ask, is there a way to restrict it to only check date's data from today's date and going back to the last 5 years? (1825 days to be exact)

 

[Peter_SSs]

Thanks for the Formulas. Many Many Thanks for your help. It worked!

We are on the right track then.

.. is there a way to restrict it to only check date's data from today's date and going back to the last 5 years? (1825 days to be exact)

I think there there will be but can you please clarify the following.

For me, TODAY()-1825 gives 17-OCT-2013

If Trip #1 was from 1-OCT-2013 to 31-OCT-2013
& Trip #2 was from 10-NOV-2013 to 5-DEC-2013

- would the counting start from 17-OCT-2013 (part way through Trip #1 )
- or would it start from 10-NOV-2013 (the start of the next trip after 17-OCT-2013
- or some other date?

 

[BlackBirdSR71]

I've been trying to figure it out for 2 weeks and your solution worked like charm! Thanks a million!

Yeah ... it would be 17th Oct 2013 (Going back exactly 1825 days from Today)

 

[Peter_SSs]

Yeah ... it would be 17th Oct 2013 (Going back exactly 1825 days from Today)

Can we change the DAYS column like this?

Excel Workbook
	B	C	D	E	F	G	H
1	FROM	TO	DAYS			Start	17/10/2013
2	12-Mar-13	24-Aug-13	0	1		365	20/05/2015
3	28-Aug-13	15-Sep-13	0	1		730	14/09/2016
4	27-Sep-13	17-Aug-14	304	1
5	20-Mar-15	22-Jan-16	308	305
6	19-May-16	4-Jul-17	411	613
7	1-May-19	12-Dec-19	225	1024
Dates (2)