date field is held as 38932 how do I export as 3rd August 06

[Dave D]

I am building up SQL statements using data in a Spreadsheet. I'm doing this
via CONCATENATE. I have a date field that appears to be 3rd August 2006 but
in the concatenate appears as 38932. How do I get it to come through as 3rd
August 2006?

 

[macropod]

Hi Dave,

Assuming the date is in A1:
=TEXT(A1,"d mmmm yyyy") will return '3 August 2006'
The formula to return the day as an ordinal number is quite a bit more complicated:
=DAY(A1)&CHOOSE(AND(DAY(A1)<>11,DAY(A1)<>12,DAY(A1)<>13)*MIN(4,MOD(DAY(A1),10))+1,"th","st","nd","rd","th")&TEXT(A1," mmmm yyyy")

You can include either of these with the balance of your concatenation formula.

Cheers

 

[Dave D]

Thanks a lot

Hi Dave,

Assuming the date is in A1:
=TEXT(A1,"d mmmm yyyy") will return '3 August 2006'
The formula to return the day as an ordinal number is quite a bit more complicated:
=DAY(A1)&CHOOSE(AND(DAY(A1)<>11,DAY(A1)<>12,DAY(A1)<>13)*MIN(4,MOD(DAY(A1),10))+1,"th","st","nd","rd","th")&TEXT(A1," mmmm yyyy")

You can include either of these with the balance of your concatenation formula.

Cheers

--
macropod
[MVP - Microsoft Word]
-------------------------

I am building up SQL statements using data in a Spreadsheet. I'm doing this
via CONCATENATE. I have a date field that appears to be 3rd August 2006 but
in the concatenate appears as 38932. How do I get it to come through as 3rd
August 2006?