Forum Discussion

totzelberger's avatar
totzelberger
Copper Contributor
Nov 01, 2021

Date Format in Excel

I am using Microsoft Office Home and Student Version 2016. My dates are currently formatted as such: 10261953. I would simply like Excel to recognize this as a date so it can be sorted by date, and f...
excel
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Nov 01, 2021

totzelberger Search on-line for "dates before 1900 Excel" and you will understand that working with pre-1900 dates in Excel isn't very straight-forward. I haven't come across a method yet that allows you to enter numbers like yours and automatically transform them to real dates. With text functions like LEFT, MID, RIGHT, CONCAT (or TEXTJOIN if your Excel version supports it), you can fairly easily create text strings that look like dates in the mm/dd/yyyy format, but they will never be seen as real dates in Excel.

=TEXTJOIN("/",,TEXT(LEFT(C2,LEN(C2)-6),"00"),MID(C2,LEN(C2)-5,2),RIGHT(C2,4))

or

=CONCAT(TEXT(LEFT(C2,LEN(C2)-6),"00"),"/",MID(C2,LEN(C2)-5,2),"/",RIGHT(C2,4))

 

Enter one of these in e.g. H2 and copy down. This will turn 6091854 into 06/09/1854

 

And if you want to perform calculations based on the dates, you'll need some more tricks as shown in the attached link.

 

https://www.mrexcel.com/excel-tips/deal-with-dates-before-1900/ 

Resources