Date Format in Excel

[totzelberger]

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 format it as mm/dd/yyyy. I have tried everything I can think of. There has to be a simple solution. Any suggestions would be appreciated. Attached is a sample of my file. Excel 2016 for Windows

[#2]

@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/