Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time
I have a field in a delimited CSV. file 20060229 and want to convert it to a
date field reading 29/02/2006. How can I do that? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time
There are only 28 days in February in 2006. You should get March 1 as
1/3/2006 using =DATE(LEFT(A1,4),MID(A1,5,2),(RIGHT(A1,2))) -- Gary's Student "DD" wrote: I have a field in a delimited CSV. file 20060229 and want to convert it to a date field reading 29/02/2006. How can I do that? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time
After you import the .csv file, you can select that field and use data|text to
columns. You can specify ymd as the field type. DD wrote: I have a field in a delimited CSV. file 20060229 and want to convert it to a date field reading 29/02/2006. How can I do that? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time
You can only make it TEXT to appear to be 29/02/2006.
=RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4) If turned into a DATE it becomes 3/1/3006 as there is no Feb 29th in 2006..... =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) Vaya con Dios, Chuck, CABGx3 "DD" wrote in message ... I have a field in a delimited CSV. file 20060229 and want to convert it to a date field reading 29/02/2006. How can I do that? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time
You can't because there's no such date as 29/02/2006 but if you have a real date e.g. 20060228 you can either... ..use Data Text to Columns and at step three choose date option and YMD. or with a formula, assuming your "date" in A1 =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=517561 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time
Sorry, 3/1/2006 not 3/1/3006...."you know what I meant"....the formula is
ok, just mine typing is tired.......bed time here in St. Petersburg, Florida...... Vaya con Dios, Chuck, CABGx3 "CLR" wrote in message ... You can only make it TEXT to appear to be 29/02/2006. =RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4) If turned into a DATE it becomes 3/1/3006 as there is no Feb 29th in 2006..... =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) Vaya con Dios, Chuck, CABGx3 "DD" wrote in message ... I have a field in a delimited CSV. file 20060229 and want to convert it to a date field reading 29/02/2006. How can I do that? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time entry with am/pm and no colons | Excel Discussion (Misc queries) | |||
Entering Times | Excel Discussion (Misc queries) | |||
Accumulate weekly time to total time in Excel. | Excel Discussion (Misc queries) | |||
Time Sheet Calculation Help Needed! | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |