Why would this formula produce this wrong answer

Month() returns a number 1 to 12, which, as dates, represent 1 through 12 Jan 1900.

=MONTH(TODAY()) gives the result 2 so TEXT function gives you the month of day 2 in the excel calendar which is 2 Jan 1900, so you get Jan. For your purposes you don't need MONTH function - use this version

=TEXT(TODAY(),"mmm")

Because it should be:

Formula:

Please Login or Register  to view this content.

Your formula is trying to evaluate the month of a date serial number of 2, which is the 2nd January 1900


Edit: wow, the forum is fast tonight!!

OllyXLS has the best answer.

I've had to do something similar to what you're asking, here's how I solved it before. Much less clean and cool than OllyXLS.

A1:A12 has the numbers 1 through 12, and then B1:B12 has the months Jan, Feb, Mar, etc. It should look like this:

1 Jan
2 Feb
3 Mar
4 Apr
5 May
6 Jun
7 Jul
8 Aug
9 Sep
10 Oct
11 Nov
12 Dec


Then in C1, use this formula:
=VLOOKUP(MONTH(TODAY()),A1:B12,2,FALSE)

This formula vlookups the "month calendar" in columns A and B and will return the current month of Today.