CODEWITHSUNDEEP
stringexceldate
Redzwan Latif
Redzwan Latif

Reputation: 886

Excel - Convert date from yymmdd to dd/mm/yy

I have an Excel file which is exported from a Access database.

I have 25000 records and I will need to replace all of them.

The date column is not formatted (yymmdd). I need to change the date format from yymmdd to dd/mm/19yy. For the yy I need to add a constant value 19 in front of it so it would be 19yy.

I have only 1 date column per row

Is there any way to convert all the 25000 record's column formatted in yymmdd to dd/mm/19yy in a few clicks?. Thank you

Upvotes: 3

Views: 20525

Answers (3)

barry houdini
barry houdini

Reputation: 46341

Assuming data starts at A2 put this formula in B2

=(19&TEXT(A1,"00-00-00"))+0

Now format B2 in required date format, e.g. mm/dd/yyyy

and you can easily "fill down" all 25000 rows by doing this:

put cursor on bottom right of B2 (first cell with formula) until you see a black "+" - that's the "fill-handle" - double click and the formula will populate as far down as you have continuous data in the adjacent column

Note: you can probably omit the 19& if all your dates are after 1930 because the default is to treat any date written without the century as 1900s if it's >=30 or 2000s if it's <30 [although you can change that in regional settings]

Upvotes: 2

Jud
Jud

Reputation: 1188

This will give you the result as an actual date which you can then format as you wish using Excel's date formatting options.

=DATE(1900+LEFT(A1,2), MID(A1,3,2), RIGHT(A1,2))

Upvotes: 6

Joe DeRose
Joe DeRose

Reputation: 3558

If you don't need to parse it into a date value, but merely need to display a date in the format you identified, the following will work on a value in cell A1 (copy down to the rest of the 25,000 values as needed:

=RIGHT(A1,2) & "/" & MID(A1,3,2) & "/19" & LEFT(A1,2)

In my cell A1, I entered the value 981116. This formula converted it to 16/11/1998. I think that's what you're looking for, right?

Upvotes: 2

Related Questions