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.
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.
At the bottom of the dropdown, More Number Formats allows you to customize the order of the elements in the date. You can also choose a particular date format based on your location.
Note: The location options must be compatible with the language of your machine.
To insert today’s date, there are two primary functions: NOW() and TODAY(). The former also includes the current time.
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.
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.
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.
In the dialog box, select “Linear” and under “Step value”, provide the desired increment.
Result: The dates will be auto filled every three days.
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)
Note: Months have to be in numeric form.