Duff Date Text

[Razoo]

Excel 2002 SP3.

Hi,

I'm having a bit of date trouble.

I have a cell (assume A1) with a date in it. The cell is formatted: Custom
mmmm yyyy and shows the date as 'April 2009'.

If I format the cell as a number, the date serial number shows as '39908'

I have another cell (assume B1) formatted as 'General' that I want to put
some text in to include the month extracted from the date in cell A1.

Formula:

="VAT Calculation for " & TEXT(MONTH(A1),"mmmm")

However, this gives me a text line in B1 that reads:

'VAT Calculation for January'

I kinda expected 'April', rather than 'January'.

If I use the following formula instead:

="VAT Calculation for Month " & MONTH(A1)

The text line in B1 reads:

'VAT Calculation for Month 4'

Any idea why month 4 is translating as 'January', rather than 'April'?

TIA

 

[T. Valko]

="VAT Calculation for " & TEXT(MONTH(A1),"mmmm")

Any idea why month 4 is translating as 'January', rather than 'April'?

Remove the MONTH function:

="VAT Calculation for " & TEXT(A1,"mmmm")

The MONTH function returns a number from 1 to 12. In your case it's
returning 4 (month number for April). The TEXT function is evaluating the
number 4 as the date serial number 4 which is January 4 1900. So:

TEXT(MONTH(A1),"mmmm") = January

 

[Razoo]

Remove the MONTH function:

="VAT Calculation for " & TEXT(A1,"mmmm")

The MONTH function returns a number from 1 to 12. In your case it's
returning 4 (month number for April). The TEXT function is evaluating the
number 4 as the date serial number 4 which is January 4 1900. So:

TEXT(MONTH(A1),"mmmm") = January

Thanks Biff, I suspected it might be pilot error!

So in effect I was evaluating the 'Month' function twice - once on the date
number 39908 using 'Month', which gave me '4', and again on the previous
result '4' using 'Text' & 'mmm', which gave me 'January'.

Thanks for your help.

 

[T. Valko]

You're welcome. Thanks for the feedback!