Duff Date Text

  • Thread starter Thread starter Razoo
  • Start date Start date
R

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
 
="VAT Calculation for " & TEXT(MONTH(A1),"mmmm")
Any idea why month 4 is translating as 'January', rather than 'April'?
Click to expand...

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
 
T. Valko said:
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
Click to expand...

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.
 
Back
Top