displaying months from cells as their text (not numerical)

**Xtopher** · 10-14-2009, 07:23 PM

G'Day,

Please refer to the attached screenshot of my working spreadsheet for help.

Please avert your attention to line 86 (month2). The formula contained within these cells equals =MONTH(C84) [the date 2 cells above it]... to which I've formatted the cell to "mmmm" and it displays "January" across the board. Can anyone explain why this is occuring and how I can fix it please? I'm aiming to graph this information and can't accurately do it using the moving monthly system i've put in place if I can't change the months dynamically like this.

Secondly, and on a side note. Line 84 currently takes todays date (not currently todays date in the example screenshot) (=TODAY()), and to get to the previous 13 months I deduct 31 days. I can foresee this as being a problem further down the track especially when leap years come into play. Is there a function in Excel where I can simply deduct 1 month from another? =MONTH(C84)-1 ?

**daddylonglegs** · 10-14-2009, 07:40 PM

When you use MONTH function that returns a number 1 to 12. When you format that number as mmmm it reads the number as the number of days from 1st Jan 1900 (that's where Excel begins counting dates). As all the numbers 1 to 12 represent dates in January 1900 you always get January.

For display of the correct month use just

=C84

and format as "mmmm" (although the underlying value is still a date it displays as the month) or use this formula to extract the month as text

=TEXT(C84,"mmmm")