Next Date in a series with known start date and frequency.

[laleph]

Help desperately needed. I don't think this should be too tough but for the life of me can't figure it out. I need a formula for Column C which will auto populate based on today's date Today().

Since we know the start date and the frequency, I just need to formula which gives me the next date in the series. Note that if a "start date" is on a day that is either the 29, 30, or 31st, we'd need the "next date" to be the last day of the month (for example, if a start date was 8/31/12, in February the monthly next date would be 2/28/13.

Looking at my sheet below,
C2 should = 1/18/2013
C6 should = 12/17/2012
C12 should = 1/5/2013
C21 should = 2/28/2013
C24 should = 10/24/2013

Any help here would be GREATLY appreciated!

Thanks!

-Lars


[TABLE="width: 405"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Start Date
[/TD]
[TD]Frequency
[/TD]
[TD]Next Date
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1/18/2006
[/TD]
[TD]Annually
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1/12/2012
[/TD]
[TD]Annually
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2/13/2004
[/TD]
[TD]Annually
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]4/5/2012
[/TD]
[TD]Annually
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1/17/2012
[/TD]
[TD]Monthly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]1/28/2012
[/TD]
[TD]Monthly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]1/30/2012
[/TD]
[TD]Monthly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]1/13/2012
[/TD]
[TD]Monthly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]5/31/2002
[/TD]
[TD]Monthly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]1/12/2012
[/TD]
[TD]Monthly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]7/5/2010
[/TD]
[TD]Monthly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]1/28/2012
[/TD]
[TD]Monthly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]11/29/2007
[/TD]
[TD]Monthly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]8/28/2006
[/TD]
[TD]Monthly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]1/5/2012
[/TD]
[TD]Monthly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]8/11/2006
[/TD]
[TD]Monthly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]1/8/2007
[/TD]
[TD]Monthly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]1/10/2012
[/TD]
[TD]Monthly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]1/1/2013
[/TD]
[TD]Monthly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]11/30/2007
[/TD]
[TD]Quarterly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]3/28/2012
[/TD]
[TD]Quarterly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]6/15/2012
[/TD]
[TD]Quarterly
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD]4/15/2009
[/TD]
[TD]Semi Annually
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Ron Coderre]

Using your sample data, try this regular formula:
C2: =EDATE(A2,LOOKUP(B2,{"A","M","Q","S"},{12,1,3,6}))

Copy that formula down through C24

These will be the results:
[TABLE="width: 396"]
<tbody>[TR]
[TD]Start Date
[/TD]
[TD]Frequency
[/TD]
[TD]Next Date
[/TD]
[/TR]
[TR]
[TD]1/18/2006
[/TD]
[TD]Annually
[/TD]
[TD]1/18/2007
[/TD]
[/TR]
[TR]
[TD]1/12/2012
[/TD]
[TD]Annually
[/TD]
[TD]1/12/2013
[/TD]
[/TR]
[TR]
[TD]2/13/2004
[/TD]
[TD]Annually
[/TD]
[TD]2/13/2005
[/TD]
[/TR]
[TR]
[TD]4/5/2012
[/TD]
[TD]Annually
[/TD]
[TD]4/5/2013
[/TD]
[/TR]
[TR]
[TD]1/17/2012
[/TD]
[TD]Monthly
[/TD]
[TD]2/17/2012
[/TD]
[/TR]
[TR]
[TD]1/28/2012
[/TD]
[TD]Monthly
[/TD]
[TD]2/28/2012
[/TD]
[/TR]
[TR]
[TD]1/30/2012
[/TD]
[TD]Monthly
[/TD]
[TD]2/29/2012
[/TD]
[/TR]
[TR]
[TD]1/13/2012
[/TD]
[TD]Monthly
[/TD]
[TD]2/13/2012
[/TD]
[/TR]
[TR]
[TD]5/31/2002
[/TD]
[TD]Monthly
[/TD]
[TD]6/30/2002
[/TD]
[/TR]
[TR]
[TD]1/12/2012
[/TD]
[TD]Monthly
[/TD]
[TD]2/12/2012
[/TD]
[/TR]
[TR]
[TD]7/5/2010
[/TD]
[TD]Monthly
[/TD]
[TD]8/5/2010
[/TD]
[/TR]
[TR]
[TD]1/28/2012
[/TD]
[TD]Monthly
[/TD]
[TD]2/28/2012
[/TD]
[/TR]
[TR]
[TD]11/29/2007
[/TD]
[TD]Monthly
[/TD]
[TD]12/29/2007
[/TD]
[/TR]
[TR]
[TD]8/28/2006
[/TD]
[TD]Monthly
[/TD]
[TD]9/28/2006
[/TD]
[/TR]
[TR]
[TD]1/5/2012
[/TD]
[TD]Monthly
[/TD]
[TD]2/5/2012
[/TD]
[/TR]
[TR]
[TD]8/11/2006
[/TD]
[TD]Monthly
[/TD]
[TD]9/11/2006
[/TD]
[/TR]
[TR]
[TD]1/8/2007
[/TD]
[TD]Monthly
[/TD]
[TD]2/8/2007
[/TD]
[/TR]
[TR]
[TD]1/10/2012
[/TD]
[TD]Monthly
[/TD]
[TD]2/10/2012
[/TD]
[/TR]
[TR]
[TD]1/1/2013
[/TD]
[TD]Monthly
[/TD]
[TD]2/1/2013
[/TD]
[/TR]
[TR]
[TD]11/30/2007
[/TD]
[TD]Quarterly
[/TD]
[TD]2/29/2008
[/TD]
[/TR]
[TR]
[TD]3/28/2012
[/TD]
[TD]Quarterly
[/TD]
[TD]6/28/2012
[/TD]
[/TR]
[TR]
[TD]6/15/2012
[/TD]
[TD]Quarterly
[/TD]
[TD]9/15/2012
[/TD]
[/TR]
[TR]
[TD]4/15/2009
[/TD]
[TD]Semi Annually
[/TD]
[TD]10/15/2009
[/TD]
[/TR]
</tbody>[/TABLE]

Is that something you can work with?

 

[laleph]

What I'm looking for in column C (Next Date) needs to be related to today. So rather than having the next date from the start date, I'm looking for the next date from right now. For example the first one should return 1/18/13, and C6 should return 12/17/12. Does this make sense?