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?

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 (E-Mail Removed)> wrote in message news:FE7C832A-B9C1-4354-8612-(E-Mail Removed)...
>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?

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 (E-Mail Removed)> wrote in message news:FE7C832A-B9C1-4354-8612-(E-Mail Removed)...
> >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?
>