How to Insert Dates in Excel and Google Sheets

This tutorial covers how to insert dates in Excel and Google Sheets.

The Date Format

Like numbers, currency, and time, the date format is a quintessential number format. Excel tries its best to auto-recognize data types, but it doesn’t always succeed.

1. To manually apply the date format to a cell or group of cells, in the Ribbon, go to the Home tab. Then expand the Number drop down and choose Short Date or Long Date.

2. The difference between short and long date formats is that the latter includes the day of the week.
   Warning! Applying the date format to inappropriate data can give you unexpected results!
3. If you would like to change the default format of the date, with the order of month, day, and year, click More Number Formats… under the Number drop down.

4. This opens the Format Cells dialog box so you can customize the order of the elements in the date. You can also choose a date format based on your Locale (location).

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

Today’s Date

1. To insert today’s date, there are two primary functions: The NOW and TODAY Functions. The former also includes the current time.

2. To change how the resulting date is displayed, follow the steps mentioned earlier to customize the format.
   Tip: You can also use the shortcut CTRL + ; for today’s date and CTRL + SHIFT + ; for the current time.

Auto-Populate Dates

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

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

2. The example demonstrates how to fill cells by day increments, but you can also increment by week, month, and year. Follow the same steps that you would use to fill cells by day increments and click the icon at the bottom-right, next to the fill handle. From the drop down, choose an increment.

The list of dates is filled in accordingly.

Fill Dates by Custom Interval

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

1. To fill cells with dates by every n days, first perform the steps as you would to autofill dates normally.
2. Then in the Ribbon, go to Home > Fill > Series…

3. In the Series dialog box, choose Linear as the Type and, under Step value, type in the increment (e.g., 3, for three days).

The result is a list of dates three days apart.

Create a Date From Separate Columns

If you 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; “January” wouldn’t work.

Insert Dates in Google Sheets

Inserting dates in Google Sheets works similarly.

1. Like in Excel, dates are stored as numbers in Google Sheets. Therefore, when you type in a date, the cell’s alignment defaults to right.

2. To get to the Google Sheets Date format, in the Menu, go to Format > Number > Date.

Customize the Date Format

1. To customize the date format, click Custom date and time.

2. The default date format is MM-DD-YYYY. Each part of the date – the month, day, and year – can be customized by clicking the small toggle button to the right of each date section.

3. Click the toggle button to the right of Month, and then click Delete.

4. Repeat this for the day and year to clear the custom format.

5. This leaves you with two forward slashes which were being used to separate the date parts. Delete these with the BACKSPACE key on your keyboard.
6. Then in the drop down, click Day to add the day back to the custom format.

7. Press the SPACE bar to put a space between the day and the next item. Alternatively, you could add a forward slash or some other symbol, depending on your final custom format.

8. Then from the drop down, choose Month.

9. Choose the Month format. Click the toggle button and choose Month as full name from the list provided.

10. Finally, press the SPACE bar once again and add the year.

11. Click Apply. You’ve created a new date format (DD MMMM YYYY).

Fill Dates in Google Sheets

If you drag a date cell down, it automatically fills in the cells below it chronologically.

Date Formulas in Google Sheets

As in Excel, you can write formulas using the NOW, TODAY, and DATE Functions in Google Sheets.