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

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?

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

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]
-------------------------

"Dave D" <Dave wrote in message ...
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?

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

Thanks a lot

"macropod" wrote:

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]
-------------------------

"Dave D" <Dave wrote in message ...
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?