I don't want julian date conversion, but EXCEL is doing it anyway

hairymonster

New member
Joined
Dec 1, 2018
Messages
2
Reaction score
0
Points
0
Excel Version(s)
Office 365
Hi everyone,

I'm trying to enter dates into a spreadsheet, but excel is treating my keyed date as a Julian date and converting it as such.

I have formatted the field as a date field to be dd/mm/yyyy

So, I would expect to be able to key 251116 or 25112016 and see this convert to 25/11/2016. Instead it converts it to 11/07/2587!!!

I don't know what I have done to make this happen. Any ideas please?

Thanks - Philip
 
Hi and welcome
Alas, things are not so simple in excel when dealing with time and dates . Read this to understand how XL treats time and date. Formatting will not help in this case.
One easy workaround
-enter 25112016 in a cell - select the cell - Data tab - Text to columns - Next -Next - In the column data format select "date" and the wanted format ( in this case DMY) - Finish
You can apply this to an entire column
 
Thanks very much.

Am I imagining it, or did Excel ever allow you to key a date in without slashes i.e. 140718 and it would format it as 14/07/18 or 14/07/2018 if the cell was formatted as a date?

Philip
 
Back
Top