What Year Is It? A Short Guide to Dates in Excel

Have you ever had trouble with dates? At least with Excel dates, we can offer some helpful tips. Dates are among the most frustrating concepts in Excel and they cause people all sorts of problems. In this article, we will show you: (i) how Excel recognizes dates, (ii) how to work with dates, (iii) how to effectively display dates, and (iv) some great date (not dating) tips.

How Excel Recognizes Dates

The first thing to note is that Excel recognizes dates as numbers (this is why dates are initially right-aligned in any given cell). Let’s call these numbers “Excel date equivalents”. For example, today’s date of January 15, 2018 is recognized as the number 43,480. Following this logic, yesterday’s date is recognized as 43,479 and tomorrow’s date is recognized as 43,481.

Naturally, the most frequent question that we get on this subject is “what date is the Excel date equivalent of 1?” The answer to this is that the first date recognized in Excel is January 1, 1900 (i.e., 1). On the opposite end of the spectrum, the last date recognized in Excel is December 31, 9999 (i.e., 2,958,465). See the table below for an illustration of these dates and their Excel date equivalents.

Working with Dates

It is important that Excel recognizes dates as numbers, as this allows the user to perform both mathematical formulas and Excel functions on them. This can be something as simple as adding the number one to a date to get the subsequent date, to something more sophisticated like using specific Excel functions to return various dates or information about the dates.

For example, there is an Excel function (EOMONTH) that can look at a date and return the last date in any user-specified month. This can be useful when building out a monthly or annual forecast model. There are also Excel functions that can extract the day (DAY), month (MONTH), or year (YEAR) from a date. See the table below for an illustration of these various techniques and their results.

Effectively Displaying Dates

While the above demonstrates how to dynamically interact with dates in Excel, it is also important to display these dates without losing the integrity of the number that Excel recognizes it as (i.e., the Excel date equivalent). For example, you may want to display January 15, 2019 as 2019-01-15, 01-15-2019, 15/01/2019, or perhaps even Tuesday, January 15, 2019. Luckily there is a way to do this in Excel using Custom Formatting in the Format Cells dialogue box.

Once the Format Cells dialogue box is opened (“Ctrl + 1”), press Tab followed by the letter “C” twice to highlight “Custom”. Press Tab once more to enter the desired Custom Format. See the table below for an illustration of the various ways that dates can be displayed as days, months, and years in Excel; as well as a few examples that combine the various day, month, and year formats to display the entire date in various formats.

Marquee Tool Tip #1

Have you ever opened an Excel spreadsheet and the dates are displayed differently on your computer than your colleagues? This is due to the date settings of your operating system.

To test this out, open up a new spreadsheet and type =TODAY() into a cell. The format in which the date first appears is based on the date settings of your operating system.

Open the Format Cells dialogue box on the cell above and a similar dialogue box to the one below should appear:

In the above screenshot there are two date formats that begin with an asterisk that are linked to your operating system – the first one on the list relates to the “Short Date” format of your operating system and the second refers to the “Long Date” format of your operating system.

Exit the Format Cells dialogue box and open up the date settings on your operating system. Confirm that the “Short Date” format of your operating system matches the format in Excel. Then try changing the “Short Date” format of your operating system and watch it update in Excel. You can also repeat this experiment with the “Long Date” Excel format and operating system settings. Note however that when you change the date settings in your operating system, you may have to press F9 to refresh the date formats in Excel.

Marquee Tool Tip #2

If you decide to manually type a date into a cell and it is not in the same format as your operating system, Excel will recognize the date as text (when this happens the date will initially be left-aligned in the cell). This is why the =TODAY() function is so powerful – not only does it return the current date (and does so whenever Excel recalculates), but it also provides you with a date in your operating system’s format that will be recognized as a date in Excel.