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

	A	B	C
1	Start Date	Frequency	Next Date
2	1/18/2006	Annually
3	1/12/2012	Annually
4	2/13/2004	Annually
5	4/5/2012	Annually
6	1/17/2012	Monthly
7	1/28/2012	Monthly
8	1/30/2012	Monthly
9	1/13/2012	Monthly
10	5/31/2002	Monthly
11	1/12/2012	Monthly
12	7/5/2010	Monthly
13	1/28/2012	Monthly
14	11/29/2007	Monthly
15	8/28/2006	Monthly
16	1/5/2012	Monthly
17	8/11/2006	Monthly
18	1/8/2007	Monthly
19	1/10/2012	Monthly
20	1/1/2013	Monthly
21	11/30/2007	Quarterly
22	3/28/2012	Quarterly
23	6/15/2012	Quarterly
24	4/15/2009	Semi Annually

<tbody>
</tbody>

 

[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:

Start Date	Frequency	Next Date
1/18/2006	Annually	1/18/2007
1/12/2012	Annually	1/12/2013
2/13/2004	Annually	2/13/2005
4/5/2012	Annually	4/5/2013
1/17/2012	Monthly	2/17/2012
1/28/2012	Monthly	2/28/2012
1/30/2012	Monthly	2/29/2012
1/13/2012	Monthly	2/13/2012
5/31/2002	Monthly	6/30/2002
1/12/2012	Monthly	2/12/2012
7/5/2010	Monthly	8/5/2010
1/28/2012	Monthly	2/28/2012
11/29/2007	Monthly	12/29/2007
8/28/2006	Monthly	9/28/2006
1/5/2012	Monthly	2/5/2012
8/11/2006	Monthly	9/11/2006
1/8/2007	Monthly	2/8/2007
1/10/2012	Monthly	2/10/2012
1/1/2013	Monthly	2/1/2013
11/30/2007	Quarterly	2/29/2008
3/28/2012	Quarterly	6/28/2012
6/15/2012	Quarterly	9/15/2012
4/15/2009	Semi Annually	10/15/2009

<tbody>
</tbody>

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?