Date Function Quirk (one month off)

I am trying to use the DATE function in combination with the TEXT function to allow a user to input a numerical value (1 through 12) and have excel convert that value into the text-based representation of the month. For example, if the user inputs 1, excel converts that to January.If the user inputs 11, excel converts that to November.


This is my formula:

=TEXT(DATE(,A1,),"MMMM")

“A1” is the cell that the user inputs a value of 1-12

The problem I have is that the DATE function is one month off. That is, if the user inputs 2, excel converts that to January (not February).If the user inputs 12, excel converts that to November (not December).The input of 1 is yields a result of “January.”(You can get January by inputting 1 or 2.And, from 2 on up, the numerical and text values are off by one month 2=January, 3=February, 4=March, etc.

Does anyone have an idea of what I have done wrong with my formula or how to fix this without subtracting 1 from the A1 value?

It's not a quirk.
Try this...
A1:A12 contains the month numbers 1 through 12
C1: =DATE(,A1,)
Copy that formula down through C12
These are the results:
What you want to do is ensure that a legitimate date is returned.
B1: =TEXT(DATE(2016,A1,1),"MMMM")

Now the DATE() sections resolve to these values:


And the Month should also be correct.

Is that something you can work with?

That fixed it

Ron,

That's perfect. I didn't realize not having the year and day was my problem. Thank you for the quick response.

redthunder2 said:

Ron,

That's perfect. I didn't realize not having the year and day was my problem. Thank you for the quick response.

Click to expand...

The year is not essential, but without a day the number calculated will always be 1 day short, having applied the default of 0 for the day (instead of 1).

If you're only looking to return the month, another way to correct this is by simply adding the missing 1, as in: =TEXT(DATE(,A1,)+1,"mmmm") but we are adding clarity problems to the formula.