3

I have a spreadsheet in Excel where there are several entries in a date column input formatted as aa.bb.yy.

How can I change these to dd/mm/yyyy?

I haven't had any success so far with using Excel's regular date formatting functions as the aa.bb.yy input isn't being recognised as a date.

  • You need to create a custom date format. – Tyson Aug 28 '18 at 10:45
  • 2
    You should probably specify what you mean by aa.bb.yy format? dd.mm.yy? mm.dd.yy? – Marie Aug 28 '18 at 15:31
8

If the format order is identical to your native date format, then you could use the standard text replace function (Ctrl+H), replace the . by - and Excel will re-parse them automatically.

Before replace:

enter image description here

After:
enter image description here

If it is not, then you can use DATEVALUE and other functions to extract the date components separately and combine them in the proper format.

Example: if xx.xx.yy is actually mm.dd.yy, then you can do:

=DATEVALUE("20"&RIGHT(A2,2)&"-"&LEFT(A2,2)&"-"&MID(A2,4,2))

This uses the YYYY-MM-DD format, which is platform- and region-independent. You'll get the raw (int) value though, so you'll need to choose the cell format manually, but after that it'll work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.