Forum Discussion
Excel changes my Date
Hi, I’m using Microsoft 365 and in excel when I try to type in a date e.g 01012014 excel changes it to 28/10/4670. I have formatted my cells to short date, I have also gone into advanced excel options to ensure that the box isn’t ticked for “use 1904 date system”. I just want to be able to type in a date and the system will automatically put it into the format that I have selected for the cell and keep the correct date. Eg I type 01012014 = excel to populate the cell like 01/01/2014. I have googled and looked into this community page but can’t seem to find the answer that I’m looking for, can anyone help please?
Sullucci 01012014 is not recognised as a date in Excel. The first zero is dropped and you are left with the number 1012014. Format that number as a date and you get October 18 in the year 4670.
You need to split 01012014 into its date components for dd, mm and yyyy. Something like 01-01-2014 or 01/01/2014. Something that Excel recognises as a date.
For instance, =DATEVALUE("01/01/2014") will result in the number 41640. Format that as a date and you will get January 1, 2014.
