| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| need held for auto date calculation for adding years and substract | New Users to Excel | |||
| New date based on one date field minus minutes in another field | Excel Discussion (Misc queries) | |||
| export .CSV with field containing double quotes? | Excel Discussion (Misc queries) | |||
| export excel data to a field in a pdf. | Excel Programming | |||
| VBA, How to open a file with date like 29 August 2004 | Excel Programming | |||
Hybrid Mode
