Return to Excel Formulas List

Insert Dates in Excel

This tutorial covers how to insert dates in Excel.

The Date Format

Like numbers, currency, time and others, the date is a quintessential number format in Excel.

Though Excel tries its best to auto-recognize data types, it doesn't always succeed. To manually apply the date format to a cell or group of cells, select the HOME menu, expand the Number dropdown and choose short or long date.

long or short

The difference between short and long date formats is that the latter includes the weekday.
Warning! Applying the date format to inappropriate data will give unexpected results!

If you would like to change the default format of the date, with the order of month, day and year, select "More Number Formats.." under the Number dropdown.

more number formats

At the bottom of the dropdown, More Number Formatsallows you to customize the order of the elements in the date. You can also choose a particular date format based on your location.

date format

Note: The location options must be compatible with the language of your machine.

Today's Date

To insert today's date, there are two primary functions: NOW() and TODAY(). The former also includes the current time.

now and today

To change the order of the resulting date, follow the steps mentioned earlier to customize the format.

Shortcut: You can also use shortcut "CTRL + ; " for TODAY() and "CTRL + SHIFT + ;" for NOW().

Auto Populate Dates

Excel treats dates and numbers in almost the same way. Because dates are implemented as serial numbers, you can perform arithmetic operations with them such as auto-populating.

Just as you would auto-populate cells with numbers, select a cell with a date and drag the fill handle through the cells you want to fill.

day auto pop 1

day auto pop 2

The example demonstrates how to fill cells by day increments, but you can also increment by week, month and year. Just follow the same steps that you would to fill cells by day increments, and then select the icon at the bottom right next to the fill handle. On the dropdown list, choose your increment.

month auto pop

month auto pop 2

Fill Dates by Custom Interval

You might want to fill dates and increment by an amount other than one unit of time.

To fill cells with dates by every N number of days, first perform the steps as you would to autofill dates normally. Then in the editing section of the HOME menu, click Fill then Series.

different increment

In the dialog box, select "Linear" and under "Step value", provide the desired increment.

dialog different increment

Result: The dates will be auto filled every three days.

result month auto

Create a Date from Separate Columns

If you happen to work with Excel data where the year, month and day are in separate columns, you can use the DATE function to merge them into one date.

DATE(YEAR, MONTH, DAY)

separate columns

Note: Months have to be in numeric form.