Learn how to insert dates in Excel using shortcut keys, auto-fill, and various Excel functions.

Handling dates in Microsoft Excel can be tricky if you don’t know how Excel processes date entries. It stores dates as numerical values, as 1 for January 1, 1900, 2 for January 2, 1900, and so on. So, if you apply date formatting to a numerical value you might get an incorrect date, or if you apply a number formatting to a date value, you’ll get a flawed value.

These misunderstandings can ultimately ruin your data analysis and data storage in Excel. Steer away from such silly mistakes by following along with the methods outlined in this article for adding dates.

Insert a Static Date Using a Shortcut

This is the quickest way to insert the current date in Excel along with different timestamp styles. This method is static. The date and time you enter won’t change automatically as you recalculate the worksheet or re-open the workbook.

If you simply need today’s date in a blank cell or within a text string you’re editing, press the Ctrl + ; keys together on the keyboard.

Excel will insert today’s date.

Now, if you also want to add the current timestamp along with the date, after adding the date value, hit the Space key.

Now, press Ctrl + Shift + ; to include the current timestamp.

Insert a Dynamic Date Using the TODAY Function

Suppose, you want one or many cells in your worksheet to contain the current date. Also, you want the dates to update automatically the next day when the worksheet is recalculated or reopened.

In this situation, you can use the TODAY function.

Enter the following formula syntax in a blank cell where you want the date and hit Enter:

=TODAY()

You’ll see the current date instantly.

The next day, press Shift + F9 to recalculate the active worksheet or F9 to recalculate all the worksheets in the workbook.

All the cells containing the TODAY function will show the present day’s date.

Alternatively, you can close and reopen the workbook to recalculate all worksheets.

Insert Date Using the DATE Function

The DATE function lets you enter a static and custom date. Find below how to use this function:

Select a cell where you’d like to enter a custom date.

Enter the following formula into the cell and hit Enter:

=DATE(2024,12,31)

The arguments in the formula above are year, month, and date. You should enter the year as 4 digits, like 2024, and the rest of the values with up to 2 digits.

Excel will add the custom date in MM/DD/YYYY format.

Insert Date in a Series Using the Fill Tool

If you need to create an attendance record or inventory dataset for a range of dates, you can use the Fill tool. It lets you create a series of date entries in a few clicks without needing you to manually type each date in the series.

Let me show you below two different ways to use this amazing no-code Excel automation tool:

Auto Fill Date Series With One-Day Increments

Let’s say, you want to populate the first 10 days of a month in a column to account for the daily stock quantities for those days.

Create a column header named Date and type in the first date of the series, like 11/1/2024.

Now, click on the cell and select the Flash Fill square located in the bottom right corner of the cell. Excel will copy the date formatting that you’ve used.

Now, drag it down to 9 more cells to create the date series of 10 entries.

Custom Auto Fill for Weekdays, Months, & Years

Sometimes, you might also want to insert the first date of every month from a starting point, let’s say 11/1/2024. Instead of entering the dates manually, you can again use the Fill tool.

Create a Months column header and enter the month and date from which you want to start the series, like 11/1/2024.

Now, select the cell and drag the Fill square down until the 13th row to populate the first dates of each month from November 2024.

The value won’t change to the first date of each month automatically. When you’re on the 13th row, click on the Auto Fill Options icon. It’s located in the lower right corner of the dataset you’ve just created.

A context menu will open. From there, select Fill Months.

Now, you’ll get the exact monthly first dates you’ve been looking for.

In the same way, if you only need the weekdays in a 30 or 31-day series, you can select the Fill Weekdays option from the Auto Fill Options context menu.

Additionally, if you want to generate yearly dates for the next 10 years starting from 11/1/2024, simply select the Fill Years option in the Auto Fill Options.

Insert Every N^th Day Using Auto Fill

Suppose, you want to create a timetable for the delivery of certain produce to a customer every 4th day of a month starting from the first day of service subscription.

Instead of manually looking up those dates from a calendar and typing the dates into an Excel worksheet, you can use the Fill Series tool to fill every Nth day, like the 4th day.

Select a cell from which you want to start the series. Enter the first date of the month.

Now, drag the fill handle down for 7 to 8 cells.

Go to the Home tab and click on the Fill drop-down menu in the Editing block.

You’ll see the Series dialog box. Keep everything unchanged. Just enter 4 in the Step value field and click OK.

You’ll get every 4th day from the start date. If there’s any date from the next month, you can delete that.

Now, if you require dates for every 3rd day or 5th day, simply enter 3 or 5 in the Step value field.

Insert Date Using a Date Picker Tool

There are many easy-to-use and free Excel add-ins to insert date or date and time in worksheets. You can use any of those from the Excel Add-ins Store.

Firstly, you must activate the Developer tab in Excel to access the Add-ins marketplace. You can check out this article to learn the steps:

📒 Read More: 2 Ways to Add the Developer Tab in Microsoft Excel

Now, go to the Developer tab and click on the Add-ins command in the Add-ins block.

The Office Add-ins window will open.

Click on the STORE tab at the top.

Now, type in Date Picker in the Search field and hit Enter.

Click on the Add button for the Mini Calendar and Date Picker add-in.

Select Continue on the dialog box that opens.

A calendar object will show up on the active worksheet.

Click on any cell and choose a date from that calendar.

Excel will insert the selected date in DD/MM/YYYY format.

A downside of this add-in is it doesn’t install as a permanent add-in that you can call from the ribbon menu when needed. If you select it and hit Delete, Excel removes the add-in. If you’re planning to use it for some time, you must use it as a floating object on the worksheet.

Insert Random Dates Using an Excel Function

Let’s say, you need to populate 10 random dates between two dates for data analytics practice purposes. Alternatively, if certain tests require you to randomize exam dates for candidates, you’ll need random dates between the start and end dates.

You can combine the RANDBETWEEN and DATE functions together to fetch random dates in Excel.

For instance, you want to generate random test dates between 11/01/2024 to 11/30/2024 in the above dataset.

So, go to the cell B2 and enter the following formula:

=RANDBETWEEN(DATE(2024,11,1), DATE(2024,11,30))

Hit Enter to calculate the first random date.

Now, use the fill handle to copy and paste the same formula to the rest of the cells in the dataset.

So, now you’ve got a series of random dates between the specified start and end dates.

Insert a Date Using the Data Validation Tool

If you’re creating a data entry worksheet where data entry operators will have to enter a date value between two dates, you can use the Excel Data Validation tool.

Suppose, you want to create a selective date entry between B2:B9 in the dataset shown above.

So, select this cell range and click on the Data Tools drop-down arrow in the Data tab.

Select Data Validation from the Data Tools context menu.

The Data Validation dialog box will open. Click on the Allow drop-down list and choose the Date option.

The Data field should be set to between.

Now, enter the required start and end date in the Start date and End date fields.

Go to the Input Message tab. Enter the required text strings in the Title and Input message fields.

Navigate to the Error Alert tab and type in the messages for the Title and Error message fields.

Click OK to finalize the Data Validation rule.

Now, whenever a data entry operator tries to enter a date value in the Shipped Date column, they can only enter values between 11/01/2024 and 11/30/2024.

Formatting a Date in Excel

Excel will display the dates according to the region settings on the PC. For example, if you’re using the English (United States) region setting, the short date in Excel will be in the MM/DD/YYYY format.

If you need to present dates in a different format, like DD/MM/YYYY, you’ll need to use custom formatting rules.

Select a cell containing the date to be modified.

Press Ctrl + 1 to bring up the Format Cells dialog box.

Select Date in the Category column of the Number tab.

On the right, below the Type section, you’ll find multiple date formats for the English (United States) locale.

Click on that drop-down menu and choose the target locale, like English (United Kingdom) where the DD/MM/YYYY format is often followed.

Now, select the 14/03/2012 date format convention below Type and click OK.

Excel will change the date formatting style according to your selection.

Conclusions

So far, you’ve learned and practiced how to insert dates in Excel using a mix of methods, like shortcut keys, functions, Excel Auto Fill tool, Excel Data Validation, third-party add-ins, and compound formulas.

If you’re a basic Excel user and the dataset you’re working on is tiny, use methods, like the Ctrl + ; shortcut key, the DATE function, or the date picker add-in.

On the other hand, if you aspire to become an expert in Excel, practice all the methods for inserting dates, as some approaches are specifically suited for certain situations and datasets.