Determine due dates in coming year based on a start date and an interval

[Skibbs7924]

I am trying to figure out how to get a list of dates based on a start date, stop date, first due date and an interval. For example, the srvc year starts on March 1. The first service visit is on 4/1. The service interval is every three months, so 4 times per year. I need to be able to create an array of the actual future due dates for that particular year.


Agr Start------------Agr End------------1st Due------------Freqency#------------#Srvce/Year--------> 1st Due------------2nd Due------------3rd Due------------4th Due
3/1/2019------------2/29/2020------------4/1/2019------------3-Months------------4.0------------------4/1/2019----------7/1/2019----------10/1/2019----------1/1/2020
3/1/2019------------2/29/2020------------7/1/2019------------6-Months------------2.0------------------7/1/2019----------1/1/2019
3/1/2019------------2/29/2020------------9/1/2019------------12-Months----------1.0------------------9/1/2019
3/1/2019------------2/29/2020------------5/1/2019------------6-Months------------2.0------------------5/1/2019----------11/1/2019
3/1/2019------------2/29/2020------------3/1/2019------------3-Months------------4.0------------------3/1/2019----------6/1/2019----------9/1/2019----------12/1/2019


Is this possible to do in Excel? From the first five columns, produce an array of dates on the same row? There could be as few as one service date and as many as twelve

Thanks in advance for any help!!!

[Richard Buttrey]

Hi,

One way
E2:

=EDATE(C2,D2)

F2: copied across

=IF(EDATE(E2,$D$2)<=$B2,EDATE(E2,$D$2),"")

[Skibbs7924]

Thank you for responding Richard. The first option produces one date but it doesn't give me an array of dates. What I'm looking for is an array of the four due dates if the interval is quarterly. I received similar results from the 2nd formula.

[Richard Buttrey]

Did you copy F2 to G2, H2,....etc?

[Skibbs7924]

Yes, I get it now. I did a little tweaking and it's working like a champ! Thank you!