ExcelBanter - Date Formatting

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Date Formatting (https://www.excelbanter.com/excel-discussion-misc-queries/209963-date-formatting.html)

Robin Gray

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

Bob Phillips[_3_]

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

Gary''s Student

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

Robin Gray

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

All times are GMT +1. The time now is 06:50 PM.