Rolling MONTHS backwards

[dhiresh]

Hi Excel Guru,

I have a spreadsheet where I select a month in cell H6, say MARCH and a formula [which is =MATCH(H6,MonthList,0)-1] calculated 2 in cell F5.
I want to then populate months in reverse order e.g FEBUARY in U6; JANUARY in X; DECEMBER in AA; NOVEMBER in AD; and so on. Rolling backwards to last 12 months.

I tried adapting the formula =INDEX(MonthList,MOD($F$6+COLUMNS($F$6:T6),12)+1) and this is not givning me what I require. It gives me April, May, June….

Is it possible to adjust this or is there an alternative way to help generate values backwards.
Can You help?

Thanking you in advance

Warm Regards
Dhiresh

[martindwilson]

one way in u6 filled across
=IF(MOD(COLUMNS($A$1:A1),3)=1,TEXT(eomonth(DATEVALUE($H$6&"/"&1),-CEILING(COLUMNS($A$1:A1)/3,1)),"mmmm"),"")

[_Josh_]

I have a spreadsheet called "Log" that I use as a log file, and keep track of the previous six months this way on the "Dashboard" sheet:

Cell Q3: =DATE(YEAR(Q4),MONTH(Q4)-1,1)
...
Cell Q7: = DATE(YEAR(Q8),MONTH(Q8-1),1)
Cell Q8: =DATE(YEAR(MAX(Log!A2:A997)), MONTH(MAX(Log!A2:A997)),1)

There is probably some clever way to do this using EOMONTH or something, but I never found one that worked.

[dhiresh]

Hi Martin,

EXCELLENT. your function works like a treat. Many thanks.

Reviewing the months after it is populated I think it will be better to include year after the months i.e if March'2013 is selcted then 12 months backwards would be February'2013, January'2013, DECEMBER'2012, NOVEMBER'2012 and so on.

Would be easy to modify your function to fit this requirement or is there another way to achieve this?

Rgds
Dhiresh

[martindwilson]

=IF(MOD(COLUMNS($A$1:A1),3)=1,TEXT(eomonth(DATEVALUE(1&"/"&$H$6&"/"&YEAR(TODAY())),-CEILING(COLUMNS($A$1:A1)/3,1)),"mmmm'yyyy"),"")
then again its probably easier to use real dates and some formating
=IF(MOD(COLUMNS($A$1:A1),3)=1,eomonth($H$6,-CEILING(COLUMNS($A$1:A1)/3,1)),"")
see attached all cells formatted custom "mmmm'yyyy"

[dhiresh]

Hi Martin,

Your functionality on the attached file works very well. Many thanks.

However, as I understand it you have used formula to populate months and years in Column 'N' and the cells are customised to mmmm'yyyy (not part of normal Excel, maybe you have added this yourself). Rolling month/year is then populated off the values in "N" so if Custom formatting is not configured correctly then the rolling does not work.

Workbook I am creating will be placed on Sharepoint and may be used by other users to update/view. I have not tested your suggested method to see if the formatting will work if hosted on another platform but to simplify this is there another way of getting months & year, ideally I like to have a list of months with year and then get the rolling off this.

Pls could you help.

Rgds
Dhiresh