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
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: to check that Excel is correctly recognizing a date, you can temporarily format the date as a number. If Excel doesn't display the date as a number it means the date is not correctly recognized.
Create date with 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":
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":
Note: Excel will only handle dates after 1/1/1900.