Wrong Month name as Text

[PoggiPJ]

This is kind of strange. The month is May, but it is being translated to January.

My range name, cfgDSWPriceFileDate, has a date of 11-May-2011.
The format of the referenced cell is dd-mmm-yyyy
The numerical value = 40674

Using the following formula,

="Prices reflect "&custCurrency&" currency for country "&custCountryDSW&" as of "&DAY(cfgDSWPriceFileDate)&"-"&[B][COLOR=Blue]TEXT(MONTH(cfgDSWPriceFileDate),"mmm")[/COLOR][/B]&"-"&YEAR(cfgDSWPriceFileDate)

The date appears in the following format
Prices reflect USD currency for country USA as of 11-Jan-2011

Everything else in the string translates perfectly. But the month is showing up as January instead of May. Any ideas why this would happen??

 

[Jonmo1]

Drop the MONTH from the Text function...

The MONTH function will only return a number from 1 to 12, No matter what date you use.
So 1 = Jan 1 1900, 2 = Jan 2 1900, 12 = Jan 12 1900..
Therefor always the month of January, regardless of the Date in the Month Function.

so just use
TEXT(cfgDSWPriceFileDate,"mmm")


Hope that helps.

 

[Jonmo1]

Also, you can get the Day Month and Year all from the Text function, so that can be reduced to

="Prices reflect "&custCurrency&" currency for country "&custCountryDSW&" as of "& TEXT(cfgDSWPriceFileDate),"dd-mmm-yyyy")

 

[PoggiPJ]

Thank you Jonmo, that worked perfectly. I used your shortened version.

I'm not sure I understand why the Month function returned January. I guess I need to read up on the function to better understand it's use.

 

[Jonmo1]

Glad to help.

My range name, cfgDSWPriceFileDate, has a date of 11-May-2011.
The format of the referenced cell is dd-mmm-yyyy
The numerical value = 40674

Here, you seem to undestand that dates are stored as Serial Numbers
40674 = 11-May-2011
so
40673 = 10-May-2011
40672 = 9-May-2011
etc...
1 = 1-Jan-1900


With me so far?


The MONTH Function
Returns the Month NUMBER of the date provided.
It does NOT return a Date...
Just a number from 1 to 12

1 = 1-Jan-1900
2 = 2-Jan-1900
3 = 3-Jan-1900
etc..
12 = 12-Jan-1900


The TEXT function, is attempting to read a DATE and return that date formatted as mmm.
It is looking for a DATE, not just a number from 1 to 12.


So
MONTH(cfgDSWPriceFileDate) returns the Month NUMBER(from 1-12) of whatever date is in that named range.
Say it's 11-MAY-2011

then TEXT(MONTH(cfgDSWPriceFileDate),"mmm") translates to
TEXT(MONTH(11-May-2011),"mmm")
TEXT(5,"mmm")
TEXT(5-Jan-1900,"mmm")
Jan.


Hope that clears it up.

 

[PoggiPJ]

Aha... that is a terrific explanation, and now I can follow exactly what happened. So that's why the month could have been anything between 1 through 12, and it still would have come back as January. Thank you for taking the time to lay that out for me!