How to Insert Date in Excel? (Shortcut + Formulas)

Inserting dates is a frequently performed task in Excel.

Whether you want to create a meeting schedule for the entire week or you want to list down the joining dates for employees working in a company, you will find yourself using dates in your Excel worksheet.

A date can be inserted in Excel by many methods. These methods either insert the date as a static date or a dynamic date.

A static date will not change once it has been inserted. On the other hand, a date inserted dynamically will change every time you open your Excel file based on the current date.

There are many different ways by which you can format an entered date.

By default, Excel picks up the date format from the operating system’s settings.

For this article, this will be DD/MM/YYYY (as that’s the default date format in my system)

In this article, we will look at how to insert the date in Excel using different methods.

Table of Contents

Method 1: Insert Date Using Keyboard Shortcuts

In this method, we will be taking a look at how to insert the date using keyboard shortcuts.

For example, we will insert the current date (which, at the time of writing this article, is 27/12/2022) in cell A1 of our Excel worksheet.

Select cell A1.
On your keyboard, press Ctrl + ; (hold the Control key and then press the semi-colon key).
Press Enter, and the current date will be inserted as shown.

Current date inserted using keyboard shortcut

Note the DD/MM/YYYY format picked up by Excel from the system’s settings. Yours may be different.

Also, the date entered using this method is static. Meaning that the date will remain fixed once it has been inserted and will not change if you open the document on another day.

So in this method, we have seen how to insert the current date using keyboard shortcuts. This method will insert the current date and may not be helpful if you want to insert a date from the past or future.

Also read: How to Apply Short Date Format in Excel?

Method 2: Insert Date Using the TODAY Function

In this method, we will take a look at the TODAY function that is available in Excel.

The TODAY function returns the current date.

As an example, we will insert the current date (which, at the time of writing this article, is 27/12/2022) in cell A1.

Select cell A1.

The formula for inserting the current date is as follows:

TODAY()

Write the formula in Excel, as shown in the screenshot.

today formula to get insert current date in Excel

As you can see, the current date has been inserted. Note that the default date format of DD/MM/YYYY still applies.

Also, the date entered using the TODAY function is dynamic. This means that the date will change automatically depending on the day the document is opened.

So if you open this document two days later, the date will automatically change to 29/12/2022

So in this method, we have seen how to insert the current date using the TODAY function. As with the previous method, this method will insert the current date and may not be useful if you want to insert a date from the past or future.

Also read: Why are Dates Shown as Hashtags in Excel? Easy Fix!

Method 3: Insert Date and Time Using the NOW Function

In this method, we will take a look at the NOW function available in Excel. Similar to the TODAY function, which we saw in the previous method, the NOW function returns the current date as well as the current time.

As an example, we will insert the current date and time (which, at the time of writing this article, is 27/12/2022 and 19:18) in cell A1.

Select cell A1.

The formula for inserting the current date and time is as follows:

NOW()

Write the formula in Excel, as shown in the screenshot.

now function to get current date and time in excel

As you can see, the current date has been inserted. Note the DD/MM/YYYY format for the date and the 24-hour format for the time.

Also, the date and time entered using the NOW function is dynamic. This means that the date and time will change automatically depending on the day and time the document is opened.

Also read: How to Add Days to a Date in Excel

Method 4: Using the DATE Function

In this method, we will take a look at the DATE function available in Excel.

The DATE Function creates and returns valid date based on the arguments provided.

This function is particularly useful if you want to know what the date will be 10 days from now, 7 months from now, or 5 years from now.

As an example, we will use the DATE function to find out what the date will be 10 days from the current date of this article (i.e., 27/12/2022).

Select cell A1.

The syntax for the DATE function is as follows:

DATE(year, month, day)

The three arguments: year, month, and day, are numbers.

In this case, the year will be 2022, the month will be 12. Since we want to know the date 10 days from now, the day argument will be 27 + 10 i.e., 37. So the formula will be as follows:

=DATE(2022, 12, 37)

Write the formula in Excel, as shown in the following screenshot.

date function to create a date using day, month and year value

As you can see, Excel has accurately calculated the date 10 days from now i.e., 06/01/2023.

Once again Excel is picking up the system’s default settings for the date format i.e., DD/MM/YYYY.

So in this method, we have seen how to insert a date using the DATE function. This is very useful for determining dates from a certain point by simply knowing how many days, months, or even years to add.

Conversely, if you want to know the date 60 days before, just subtract those days from the current day and use that value as the ‘day’ argument of the DATE function.

Also read: How to Remove Year from Date in Excel?

Method 5: Inserting Multiple Dates Using Auto-Fill Options

In this method, we will look at the Auto-Fill option to insert multiple dates quickly.

We will look at an example where we will quickly fill cells A1 to A10 with dates that are in succession starting from 27/12/2022.

Select cell A1.
Enter the following date manually: 27/12/2022

Click and drag the Fill Handle from cell A1 to cell A10.
The dates will be inserted as shown.

Click on the small Auto Fill Options button that appears at the bottom.

The Auto Fill Options menu will appear.

From this menu, select the Fill Days option. (You can choose Fill Months or Fill Years if you want successive dates to be months or years apart.)

The end result will look like the one shown below.

So in this method, we have seen how to quickly fill cells in a column with successive dates using the Auto Fill Options menu.

This method is particularly useful for quickly adding dates to a large data set.

You can choose the Fill Weekdays options from the Auto Fill Options menu to skip dates coming on Saturdays and Sundays, which by default are considered Weekends in Excel.

Also read: Convert YYYYMMDD to MM/DD/YYYY in Excel

Method 6: Inserting Multiple Dates Using the Fill Option

In this method, we will look at the Fill option available in the toolbar to insert multiple dates quickly.

We will look at an example where we will quickly fill cells A1 to A10 with dates that are in succession starting from 27/12/2022.

Select cell A1.
Enter the following date manually: 27/12/2022

Highlight cells A1 to A10 as shown.

Go to the Home tab.

Under the Editing section, click on the Fill drop-down option.

The following drop-down menu will appear.

Select the Series option.

The Series window will appear as shown.

Under the ‘Series in’ section, select the Columns option.

Under the ‘Type’ section check the Date option.

Under the ‘Date unit’ section check the Day option.

In the ‘Step value’ field, type the value 1.

Click on OK.

The end result will look like the one shown below.

So in this method, we have seen how to quickly fill cells in a column with successive dates using the Fill Options menu.

As with the previous method, this method is also useful for quickly adding dates to a large data set.

So in this article, we have seen different methods by which we can insert dates in Excel.

Method 1 is the simplest and quickest way to insert the current date. Plus, the date entered this way is static and will not change if you open the document on another day.

Methods 2 & 3 use a formula for entering the date. The date entered this way is dynamic, meaning that it will update to the current date whenever you will open your document. This is pretty useful to have if you want to have a print date on your Excel sheet and not have to worry about changing the date every time.

If, however, you want to insert a date from the past or future, you will have to do that manually, and Methods 1, 2 & 3 will not be useful.

Methods 5 & 6 use the Auto Fill and Fill options to fill a column with successive dates quickly.

If your dataset is large, go with Methods 5 & 6. Method 4 is unique.

If you want to calculate the date, either in the future or in the past, from a certain point based on the number of days, years or months, go with Method 4.

Other Excel articles you may also like: