ExcelBanter

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

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

- - date (https://www.excelbanter.com/excel-discussion-misc-queries/54452-date.html)

flow23

date

opening a CSV file into Excel.

I need to convert one column full of dates.
I tried the text to columns function.. however it only changes the date with
two digits in day ( eg. 13022005 is converted to 13/02/2005) but the dates
with single digit day ( eg. 9022005 are not converted to 9/2/2005)

any help pls

Widemonk

date

May not be the most efficient wany but I would use...

=DATE(RIGHT(A1,4),IF(LEN(A1)=8,MID(A1,3,2),MID(A1, 2,2)),IF(LEN(A1)=8,LEFT(A1,2),LEFT(A1,1)))

(assuming your date string is in cell A1)

"flow23" wrote:

opening a CSV file into Excel.

I need to convert one column full of dates.
I tried the text to columns function.. however it only changes the date with
two digits in day ( eg. 13022005 is converted to 13/02/2005) but the dates
with single digit day ( eg. 9022005 are not converted to 9/2/2005)

any help pls

Sandy Mann

date

Slightly shorter:

=DATE(RIGHT(A1,4),MID(A1,LEN(A1)-5,2),LEFT(A1,1+(LEN(A1)=8)))

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk

"Widemonk" wrote in message
...
May not be the most efficient wany but I would use...

=DATE(RIGHT(A1,4),IF(LEN(A1)=8,MID(A1,3,2),MID(A1, 2,2)),IF(LEN(A1)=8,LEFT(A1,2),LEFT(A1,1)))

(assuming your date string is in cell A1)

"flow23" wrote:

opening a CSV file into Excel.

I need to convert one column full of dates.
I tried the text to columns function.. however it only changes the date
with
two digits in day ( eg. 13022005 is converted to 13/02/2005) but the
dates
with single digit day ( eg. 9022005 are not converted to 9/2/2005)

any help pls

All times are GMT +1. The time now is 05:22 AM.