Why do dates not work properly in Microsoft Excel?

Jul 6, 2023 | Excel Hints and Tips

This blog arises from comments I get quite regularly when talking about dates in Excel.  Why do they not sort or filter correctly?  Or, when I try to format them it doesn’t work?  All will become clear when you understand how dates operate.

How should I type in dates?

This is actually the issue that needs to be addressed.  If the dates are not typed in a way that Excel understands that they are a date then they cannot behave like a date.  There are many ways that dates can be entered but also a few that will not work.

Correct Layouts

Any of the following will work with variations using 1 or 2 figures where appropriate for the day or month and 2 or 4 figures for the year.  These examples are for the 4th of March 2023.

04/03/23

04-03-23

4 Mar 23

4 March 23

04/03

Incorrect Layouts

The following are the 3 most commonly used date layouts that Microsoft Excel does not understand as dates and will treat as text instead.

04.03.23

4th Mar 23

4th March 23

Although we frequently write dates using full stops to separate the day, month and year this does not work in Excel.  Neither does adding st, nd, rd etc to the day of the month.

Entering dates without the year

There is a different issue to be aware of if the date is entered with out the year component.  For example 4th March 2023 is entered as 04/03.  Excel will assume that the date is in the current year which will probably be what you require most of the time, and it’s quicker to type if you miss off the year.  If however you are the near the end of one year and entering dates for the next year this would be an issue or similarly if the new year had just started and you were adding information related to the year before.

The solution is to add the year component to the date.

When I type in a date it changes how it looks

If Excel recognises the data that you have entered as a date then it will format it into one of a number of date formats.  For example, 04/03 will be displayed as 04-Mar or 04/03/23 will show as 04/03/20203 or 4 Mar 23 will display as 04-Mar-23.

To ensure all valid date layouts show in the same way regardless of how they are entered the cell that will be containing the dates can be formatted into a specific date format.

  1. Select the cells that will be containing the dates or already contain dates
  2. From the Home tab, in the Number group change Custom to short date or long date.  If the drop down says something different to Custom that is fine, it will depend on the format that is already applied to the cells
Short and long date format image

3. The dates will now all display in the format you have chosen

4. If those are not suitable select More Number Formats from custom, choose the Date category and select the format required from the longer list provided

Other date formats image

NOTE:  There is a date format that shows as 4.3.23.  This does NOT mean that dates can be typed in like this but they can be displayed in this format if required.

When I type in a number it displays as a date

This will be caused because the cell you are typing in has either been formatted as a date in the past or a valid date layout has been typed in that cell before (this automatically applies a date format to the cell).  To solve this the format needs to be changed to general.

  1. Select the cell(s)
  2. In the Number group select Custom and change this to General

Further Reading

If you’ve enjoyed reading about why dates don’t work properly in Excel, there are some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.