Excel DATE function

Excel provides many versatile functions which enable you to perform a wide variety of cal­cu­la­tions. For ref­er­en­cing purposes, date and time in­form­a­tion is often included in these cal­cu­la­tions. Excel uses an internal date format for this purpose. Using the Excel formula DATE, you can ensure that a date will always be correctly displayed and that it can be read and un­der­stood by other functions.

The way dates are written can differ between countries. For example, in the US the month is usually written before the day (e.g. May 10, 2020, or 5/10/2020), while the UK puts the month in second position after the day (e.g. 10 May 2020 or 10/5/2020). Some situ­ations may require the month to be written as a number (e.g. 10/5/2020). Other times, it may be necessary to display the day of the week (e.g. Sunday, May 10, 2020).

It’s possible to enter a date as text, but the option is only suitable if you’re dealing with just a handful of dates and times. And if you need to change the format of a date at some point in time, it’s much more efficient to enter the date in a universal format. Re­gard­less of how it is displayed, the program iden­ti­fies the value as a date and saves the entry in­tern­ally in its own format. Using the DATE function, Excel then enables you to change how a date is written. The cor­res­pond­ing value can also be applied to other functions and used in cal­cu­la­tions.

Excel saves dates in­tern­ally as serial numbers - typically as the number of days since January 1, 1900. In our example, Excel saves May 10, 2020 as “43961” which is the number of days between January 1, 1900 and May 10, 2020. Times, on the other hand, are recorded as fractions of days. The entry “May 10, 2020 at 12:00 pm” would be saved in­tern­ally as “43961.5”. By saving dates as serial numbers, Excel can format them as desired and perform cal­cu­la­tions more easily.

You can enter a date directly in a cell using the following formula:

It is important to enter all four digits of a year. If you only enter two digits, Excel won’t know which century you’re referring to. Does the number 20 refer to 1920 or 2020? By entering 2020, it is clear which century you mean.

An in­ter­est­ing quirk in Excel is that you can enter values for days or months that are too large or too small. For example, if you enter the date as May 40, 2020, Excel will auto­mat­ic­ally calculate that to be June 9, 2020. It is also possible to enter negative values. In this case, Excel would calculate the cor­res­pond­ing earlier date. However, it is not possible to enter a date before 1900. If the year is between 0 and 1899, Excel will add 1900 years to it. For example, if you enter the year 1800, Excel will calculate that to be the year 3700.

You can select one of the pre­defined date formats or create your own. To define your desired date format, follow the steps below:

1. In the category list on the left, select “Custom” at the bottom of the list.
2. Type the format code in the “Type” field.
3. Click OK.

The following table contains the most important format codes:

In addition to these format codes, you can also add any other text. But you should enclose the text in quotation marks to ensure that Excel does not interpret the letters in the text as format code. This makes it possible to recreate any date format whether it be with hy­phen­a­tion points, slashes, hyphens or any other printable character.

Sometimes dates are saved as text in Excel spread­sheets, such as after importing a database. You can use the DATEVALUE function to convert the date rep­res­en­ted by text into a proper date format (i.e. the internal Excel serial number rep­res­ent­ing the date):

The value returned when using DATEVALUE is the date in the typical Excel format (i.e. the serial number). As described above, you can format this date value in your desired date format. Once again, you can use cell ref­er­ences for the returned values. To do so, simply enter the cell reference in the parametre of the cor­res­pond­ing function.

Excel is pro­grammed to auto­mat­ic­ally format any text entries that look like a date as a date. It does not matter whether the month is written as a number or a word. So, how do you know whether Excel has in­ter­preted the written date as a date or as text? If Excel has converted the text entry into an internal Excel DATE, the entry displayed will be aligned to the right. If it remains aligned to the left, Excel did not recognise the entry as a date and thus has stored it as text.

Direct input is par­tic­u­larly useful if you want to enter the time as well as the date in the same cell. To do so, simply enter the date followed by a space and the time. For time, you need to separate the hours and minutes using a colon.

Example: May 10, 2020 12:00

This date and time entry can be formatted in the same way as the date entries which do not indicate times.

You can have the current date or time displayed auto­mat­ic­ally. There are two ways to ac­com­plish this: one is dynamic where the date is auto­mat­ic­ally updated, while the other is static where Excel does not update the value.

While you can use the two formulas TODAY and NOW for dynamic entries, you need to use the keyboard shortcut [Ctrl] + [;] or [Ctrl] + [Shift] + [:] for a static entry that will not change.