Excel: TEXT((2*29), "MMMM")=February???

[Engywook]

I’m using an Excel formula to convert numbers 1 through 12 to the month names. Easy enough to do, as described here.

So, if my date in A2 is February 2, 2011 (or 40567 for the pedants) MONTH(A2) is 2. Makes sense so far.

However, TEXT(MONTH(A2), “mmmm”), which I would read allowed as “format MONTH(A2) as the full month name”, gives January.

The missing part, which I don’t get: MONTH(A2) has to be multiplied by 29:

*TEXT(MONTH(A2)29, “mmmm”) does get me February. Good to know, works perfectly, and saves me work… with one nagging problem. I want to know why. Text(29, “mmmm”) is January; Text(58, “mmmm”) is February. Seems a bit wonky to me… what’s the reason for this? And why 29?

[Rumor_Watkins]

@Engywook

I’m using an Excel formula to convert numbers 1 through 12 to the month names. Easy enough to do, as described here.

So, if my date in A2 is February 2, 2011 (or 40567 for the pedants) MONTH(A2) is 2. Makes sense so far.

However, TEXT(MONTH(A2), “mmmm”), which I would read allowed as “format MONTH(A2) as the full month name”, gives January.

The missing part, which I don’t get: MONTH(A2) has to be multiplied by 29:

*TEXT(MONTH(A2)29, “mmmm”) does get me February. Good to know, works perfectly, and saves me work… with one nagging problem. I want to know why. Text(29, “mmmm”) is January; Text(58, “mmmm”) is February. Seems a bit wonky to me… what’s the reason for this? And why 29?

Probably because the 29th day of the year is in January, whereas the 58th day is in February. (in the year 1900)

[Duke]

Isn’t that formula actually returning what month that day of the year would fall in? =text(365,“mmmm”) returns “December”, so it would make sense that Day 29 would be January and Day 58 would be February. I don’t think the number 29 actually has anything to do with it.

[MikeS]

I think Excel is giving you the month of “Day 2” in its internal representation. The starting date, “Day 1”, is some time in January 1900 (or some other year, depending on your system.) Multiplying by 29 means that you’re looking at Days 29, 58, etc. of that year instead, which are in January, February, etc.

[Canadjun]

Reading the Excel help, I don’t think you want MONTH in there, just TEXT(A2,“mmmm”)

@Excel help

Display days, months, and years To display numbers as date formats (such as days, months, and years), use the following codes in the format_text argument.
m Displays the month as a number without a leading zero.
mm Displays the month as a number with a leading zero when appropriate.
mmm Displays the month as an abbreviation (Jan to Dec).
mmmm Displays the month as a full name (January to December).

[Rumor_Watkins]

the reason you’re multiplying by 29 is because that’s a number that will synch up 1-12 to their appropriate months in 1900. (28 and 30 will work as well in 1900)

by multiplying, say 6 (the 6th month) by 29 days, you’ll get 174. Which is June 22.
edit: you don’t even need the text formula. just multiply the “number months” by 29, put the answer in another column, adn format the number in that column as “mmmm”

[Rumor_Watkins]

@Canadjun

Reading the Excel help, I don’t think you want MONTH in there, just TEXT(A2,“mmmm”)

that won’t work, because excel treats the day in A2 as if it’s the universal “excel date” (i.e. Jan 2, 1900)

[Engywook]

Yep, that’s exactly what it is… Excel is assuming that I am converting the nth day of the year, not the nth month as I would have thought reasonable.

Candadjun, you are right, simply using A2 without any multiplication would get the same results… so knowing as I do now, my formulas have a superfluous step. Changing that now in case somebody sees this and mocks my “programming.”