Forum Discussion
Changing date from dd.mm.yy to dd/mm/yy
Is there a way to formulate dates in old spreadsheets that have been recorded in the dd.mm.yy format to dd/mm/yy? Excel does not recognise the dd.mm.yy. format for sorting and is sorting as numerical values.
Very old spreadsheets I am using, they have been recording the date this way for years :(
Maybe try using a formula like this (See attached file for additional reference):
=DATEVALUE(LEFT(A2,2)&"/"&MID(A2,4,2)&"/"&RIGHT(A2,2))
Thank you Matt Mickle! This worked :) :) :)
- Glad you were able to get it working! Feel free to post back if you need any additional assistance.
Please refer attached image file.
1. What is your system date format. In my PC it shown as 21-05-2018 (i.e dd-mm-yyyy) format.
here my date separator is '-'. in some PC it is '/'.
So select that date column and replace dots (.) to your system date separator. then, you can sort as you want.
