Rolling months based on TODAY()

[3G]

Hello-

Trying to set up a chart of rolling months based on the date. I've consulted the post on the rolling months, and, have changed teh COLUMNS to ROWS (my data is organized vertically so I can do a specific chart). However, rather than selecting a current month, I just want the date in A1 to auto update by using TODAY(). I'm running into a problem with the MATCH function though because the example matches on the 1st of the month (as in the example).The formula in A2 says the following:


=DATE(2011,MATCH($A$1,lst_Months,0)+ROWS($A$2:A2),1)


I would like to have a 6 month rolling, again, taking the MONTH of TODAY() and doing + 6 months. I can figure out how to update the range...it's just the month thing that has me perplexed!


Thanks

3G

 

[Mike86]

Something like:


=month(today())


will give you today's month. Add 1 through 6 to that in subsequent cells and you'd have a rolling list of the numbers of the next six months.


This would give you a date value for the fist day of this month:


=date(year(today()),month(today()),1)


and here's next month:


=date(year(today()),month(today())+1,1)


If you're just looking at the month numbers, a 13 will pop up in Dec with this method.


However, Excel compensates for that funny month value in the Date function and will increment to 01Jan of the next year.

 

[3G]

Son of a gun. Thanks Mike. That did it. I was just doing MONTH(TODAY())...


Thanks!!


3G

 

[Elesario]

Hi 3G,


If I understand correctly you want to get the starting month of the list so your rolling list can auto-update based upon today's date. I assume the rolling portion of your list is working fine, with the switch from horizontal to vertical data.


Here's a couple of ways you can convert today's date to get the current month name.


=UPPER(TEXT(TODAY(), "mmm"))


or


=INDEX(lstMonths,MONTH(TODAY()))


You can replace the 'TODAY()' function in both of them with a reference to your date cell '$A$1'.


The first only works because the month names match the short name style used in the list. The match function is not case sensitive, I just put that in so the month displayed looks the same as the list.


If you happen to change the list to something other than months then the second function is more resilient.

 

[Luke M]

I'm not complete sure this is right, but if you're wanting 6 cells, with the first of the month, based off of today's date, would this work (copy down as needed):

=EOMONTH(TODAY(),ROW(A1)-2)+1

 

[Elesario]

Looking at the Rolling Month article and the example sheet provided (http://chandoo.org/wp/2010/04/06/rolling-months/) the months are just text strings, and not dates with formatting to show the month name only; which is why my previous solution was just to get the name of the current month.


If you're setting up month names to display from underlying date data (number format = mmm), then the solutions that determine the first of the month and incremental months are better. This would be a solution I'd like in my day to day reporting, because I could use the underlying dates in the headers to describe measurable date ranges to be used in calculations.