excel - Rolling Date Macro -

i'm using office 2003 create rolling forecast. have starting point , end point time interval (yr/month yr/month) need populate automatically @ intervals (i need macro can assign button or of sort). not know how code vb, , i'm having trouble trying formulate way record along these lines. so, use help.

to start, need interval 201308-201412

every month need drop past month (eg next month interval become 201309-201412)

once start of interval became 201404, end of interval jump 201512.

this process continues on, past month dropped beginning of interval.

once 201504 becomes beginning of interval, 201612 becomes end of interval (what had been 201512 before past month dropped).

also, because lines getting deleted/added depending on month, there way make lines inserted again avoid overlapping data? (i using tm1, , have dbrw formulas traveling these dates- if you're familiar citrix/tm1)

let me know if can explain else.

thank you!

i don't think need macro here. nested built-in function do.

formula starting interval:

=text(date(year(today()),month(today())+1,1),"yyyymm") 

this returns date formatted "yyyymm" of next month today

formula ending interval:

=if(month(today())>4,text(date(year(today())+1,12,1),"yyyymm"),text(date(year(today()),12,1),"yyyymm")) 

this checks if today's month greater 4, if move end interval next year's december otherwise keep year's december.

there several formulas used here:

text(value,format) - returns formatted text date(year,month,day) - returns date year(date_serial) - returns year date serial month(date_serial) - returns month date serial day(date_serial) - returns day date serial if(logic,true,false) - if statement in excel today() - returns today's date serial