Excel date

In Excel's date system, dates are serial numbers beginning on January 1, 1900. January 1, 1900 is 1, January 2, 1900 is 2, and so on. More recent dates are much larger numbers. For example, January 1, 1999 is 36161, and January 1, 2010 is 40179. The table below shows a few examples of dates and their corresponding serial numbers:

Because dates are just numbers, you can easily perform arithmetic on dates. For example, with the date June 1, 2020 in A1, you can add 10 days like this:

=A1+10
=43983+10
=43993
=June 11, 2020

And subtract 7 days:

=A1-7
=43983-7
=43976
=May 25, 2020

Date formats

Because Excel dates are serial numbers, you'll sometimes see the raw numbers on a worksheet when you expect a date. To display date values in a human-readable date format, apply a number format of your choice. The easiest way to choose a date format is to use the shortcut Control + 1 to open the Format Cells dialog box:

If you can't find the format you need in the list, you can use a custom number format to display the date.

Dates not recognized

A common problem in Excel is that dates are not correctly recognized, usually because Excel thinks the dates are text. This article explains various ways to convert text values to proper dates.

Note: One way to quickly check that Excel is correctly recognizing a date, is to temporarily format the date as a number, by applying the General number format with the shortcut Control + ~. If Excel is interpreting the value as a date, you should see a date serial number, as explained above. If Excel doesn't display the date as a serial number it means the date is not correctly recognized. See this article for more information.

Create a date with the DATE function

You can use the DATE function to create a date with a formula using individual year, month, and day components. For example, the following formula creates the date "March 10, 2020":

=DATE(2020,3,10)

Dates with Times

Dates can include times as well, since time values are just fractions of a 24-hour day. To create a date with a time using a formula, you can use the DATE and TIME function together. For example, the following formula creates the date value for "March 10, 2020 9:00 PM":

=DATE(2020,3,10)+TIME(21,0,0)

1900 problem

Excel erroneously treats 1900 as a leap year. This is due to a legacy bug from compatibility with Lotus 1-2-3, an older spreadsheet application that also erroneously treated 1900 as a leap year. This means the serial date 60 corresponds with the date 29-Feb-1900. Although this is a valid leap day in the Julian calendar, it is not in the Gregorian calendar we use today. This problem makes certain date calculations in the year 1900 unreliable. For example, using the DAYS function to check the days between 29-Feb-1900 and 1-Mar-1900 should return 1, but DAYS incorrectly returns 2:

=DAYS("1900-03-01","1900-02-28") //returns 2

Another example is counting the total days in 1900 by subtracting the date January 1, 1900 from the date January 1, 1901. The result should be 365 but is in fact 365:

=DATE(1901,1,1)-DATE(1900,1,1) // returns 366

To be clear, this problem only affects certain date calculations that "touch" the first 60 days of 1900. Date calculations after the first 60 days are reliable.