Dates are one of the most likely causes of problems in an Excel spreadsheet. Depending on a number of factors, Excel may or may not recognise your dates. So if Excel refuses to change the format of your date, or the sort order of dates doesn’t make sense, it is probably as a result of Excel not seeing it as a date. So what is the safest way to enter a date in Excel?
The simple answer is to enter it in short form with the month in words.
So if you want to enter 5th January 2016, type 5 Jan 2016 and click enter. Below you will see that if we do this in cell B1, it will (on its own) change to what we have in cell B3, that is a date with – in between.
The benefit of this is that Excel doesn’t need to guess which is the month and which is the day and it doesn’t depend on the regional settings of your computer.
So as an example, on my computer, if I enter the date as 2016/01/05 it recognises it as a date (see below in C2 we can add 1 day to the date I entered in B2). However, if I enter the exact same date but the other way (05/01/2016) then it doesn’t recognise it. This all depends on how my regional settings have been set up and where in the world my computer thinks it is.
Also, because of the different date conventions this could be the 5th Jan 2016, or the 1st May 2016.
By entering the date with the month in short form words, you eliminate a few things that can go wrong with dates! This is the safest way to enter a date in Excel.