#1   Report Post  
Posted to microsoft.public.excel.misc
DD
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Gary''s Student
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
CLR
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
daddylonglegs
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
CLR
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
time entry with am/pm and no colons Cyrus Excel Discussion (Misc queries) 9 March 3rd 06 12:42 PM
Entering Times Denise Excel Discussion (Misc queries) 9 November 15th 05 05:57 PM
Accumulate weekly time to total time in Excel. delve Excel Discussion (Misc queries) 0 May 4th 05 08:14 PM
Time Sheet Calculation Help Needed! sax30 Excel Worksheet Functions 2 April 26th 05 08:08 PM
time formatting and time categorizing (vlookup or data validation) MarianneR Excel Worksheet Functions 4 November 18th 04 04:24 PM


All times are GMT +1. The time now is 01:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"