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 from today. 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="class: cms_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]
[/TR]
</tbody>[/TABLE]
Since we know the start date and the frequency, I just need to formula which gives me the next date in the series from today. 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="class: cms_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]
[/TR]
</tbody>[/TABLE]