#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Date Formatting

I have a csv file which when opened with excel shows dates as 15041978 or
4051949. I am struggling to convert these to the normal format 15/04/1978
or 04/05/1949.
Any suggestions.

TIA


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,420
Default Date Formatting

Maybe a formula of

=DATE(RIGHT(A1,4),MID(A1,LEN(A1)-5,2),LEFT(A1,LEN(A1)-6))


--
__________________________________
HTH

Bob

"Robin Gray" wrote in message
et...
I have a csv file which when opened with excel shows dates as 15041978 or
4051949. I am struggling to convert these to the normal format
15/04/1978 or 04/05/1949.
Any suggestions.

TIA



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default Date Formatting

a tiny trick to make the parsing easier:
In B1:
=IF(LEN(A1)=8,A1,"0" & A1)
in C1:
=DATE(RIGHT(B1,4),MID(B1,3,2),LEFT(B1,2))

Format as you like.
--
Gary''s Student - gsnu2007k


"Robin Gray" wrote:

I have a csv file which when opened with excel shows dates as 15041978 or
4051949. I am struggling to convert these to the normal format 15/04/1978
or 04/05/1949.
Any suggestions.

TIA



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Date Formatting

Thanks Guys

"Robin Gray" wrote in message
et...
I have a csv file which when opened with excel shows dates as 15041978 or
4051949. I am struggling to convert these to the normal format
15/04/1978 or 04/05/1949.
Any suggestions.

TIA



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
conditional formatting for cell date to equal today's date Sistereinstein Excel Worksheet Functions 2 September 10th 12 08:53 PM
Date Formatting theresa317 Excel Worksheet Functions 3 April 17th 08 01:07 AM
Formatting as date gwplotts via OfficeKB.com Excel Worksheet Functions 3 April 17th 07 04:09 PM
Date formatting Melissa Excel Discussion (Misc queries) 2 November 9th 06 06:37 PM
Help with formatting the date. Lee Excel Discussion (Misc queries) 6 February 24th 06 08:27 PM


All times are GMT +1. The time now is 09:17 PM.

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

About Us

"It's about Microsoft Excel"