How to Insert Date in Excel (7 Simple Methods)

There are several purposes for inserting dates in Excel; tracking any information or for any date-time calculation you need to insert dates. Today we are going to show you various ways to insert date in Excel. For this session, we are using Excel 2019, feel free to use yours.

Overview - How To Insert Date In Excel

Here you can see there are several features like keyboard shortcuts or functions to insert dates. We will try to demonstrate all of these in the following sections.

Note that our data is a compact one that we have changed depending on the context of examples.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Insert Date in Excel

1. Keyboard Shortcuts to Insert Date

Let’s get to know about the keyboard shortcuts that insert dates in Excel.

To insert the date the shortcut will be CTRL + ; (semicolon)

Date insert key - How To Insert Date In Excel

Let’s hit the keys in Excel.

Date inserted - How To Insert Date In Excel

We are preparing this tutorial on September 28, 2021. The shortcuts provided us with the current date.

If you want the current time along with the date, then the keyboard shortcut will be

CTRL+; (space) CTRL+SHIFT+;

Date & time shortcut - How To Insert Date In Excel

SPACE works as the delimiter that separates the date and time value. Use the keys, you will find the current date and time.

Date & Time inserted - How To Insert Date In Excel

It was 4:11 PM in our time zone when we used the shortcut keys.

Note that you need time only, then use CTRL+SHIFT+;

2. Basic Excel Functions to Insert Date

Excel provides several built-in functions to insert dates. Let’s explore them.

I. TODAY Function

We can use the TODAY function to insert dates. As the name suggests, TODAY returns the current date. To know more about the function, visit the TODAY function.

Let’s use the function.

=TODAY()

TODAY function - How To Insert Date In Excel

Using this function, we have found the date (the current date).

One thing to note, this function changes the date automatically. If you open the workbook a couple of days later you will find the date of that day. For a better understanding, you can visit this article regarding automatic change of dates.

II. NOW Function

You can use the NOW function to insert dates. It will also provide the current date, in addition to that, it returns the current time. Check this NOW article for further information.

Now, let’s use the function.

=NOW()

NOW Function - How To Insert Date In Excel

By the time, we have used the function, it’s 5:02 PM here. It provides the current date and time. This value will also be changed automatically.

3. Insert date using DATE Function

To insert dates we can use another function called DATE. The DATE function forms a valid date from an individual year, month, and day component. To know more about this function, visit this article: DATE.

Let’s use the function where we will provide day, month, and year values manually.

=DATE(2021,9,28)

Direct entry DATE - How To Insert Date In Excel

Here, 2021 is the year, then 9 and 28 are the month and day respectively. We will find the date in the format showing in the following picture.

DATE formula result - How To Insert Date In Excel

Note that you can use any other format to represent the date. Open the Format Cell dialog box (shortcut: press CTRL + 1)

Format Cells dialog box - How To Insert Date In Excel

You will find different date formats there. Use your preferred one. For the time being, we are leaving it as it is.

For inserting dates, we can combine the DATE function with TODAY. The formula will be in such a way that DATE will fetch the year, month and day, produced from TODAY.

YEAR, MONTH, and DAY functions will be handy for that. To know about the functions visit the articles: YEAR, MONTH, DAY.

Now, let’s explore the formula

=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))   

DATE- TODAY formula - How To Insert Date In Excel

The YEAR fetches the year value from the result of TODAY. And the MONTH and DAY fetches the month and day value respectively from the result of TODAY.

Then the DATE function returns the date.

DATE-TODAY formula result - How To Insert Date In Excel

Note that, the date from this formula will change automatically. You can use NOW instead of TODAY but that will increase complexity to the operation.

4. Exercise AutoFill Feature

We can use the Excel AutoFill feature to insert dates. First of all, insert any date you want.

Insert a date - How To Insert Date In Excel

We are going with the current date (September 28). Selecting the cell you will see the AutoFill Handler at the bottom right side. Clicking it drag down to the cell you want to insert dates.

AutoFill dates - How To Insert Date In Excel

Here we have found the following dates.

5. AutoFill Dates with Intervals

In the earlier section, we have auto-filled dates without any interval. If you want dates after a regular interval, then selecting the dates (what we have inserted using AutoFill) click Series from Fill option in the Editing section of the Home tab.

Series option - How To Insert Date In Excel

Series dialog box will come in front of you. Set your desired interval value in the Step Value field there.

Step value set

Here we have used 2 as Step Value. Click OK, it will provide every second date from the previous one.

Autofill with intervals - How To Insert Date In Excel

Another quick way to do this is to insert two dates having an aperture.

Insert two dates

Now use the AutoFill feature.

AutoFill with interval - How To Insert Date In Excel

You will find dates after a regular interval.

6. Insert Date Depending on Adjacent Cell

We can form a formula that will help us insert dates depending on the adjacent cell. For showing the example, we will use the following dataset.

Data for insert dates in adjacent cells

Once we insert “Yes” in the Purchased column we will find the date in the Date column.

Our formula will be the following one

=IF(C4="Yes",IF(D4="",NOW(),D4),"")

We have used the IF function to execute condition-based time insertion. Read this IF article to know about this.

Formula result for insert dates in adjacent cells

We have checked whether C4 is having “Yes” or not. If there is “Yes”, then check whether D4 is empty or not. For being empty we set NOW for the cell.

Drag the formula to the next cells of this column, so the operation can be performed there as well.

Formula result for insert dates in adjacent cells 2

We have inserted “Yes” in another couple of cells from the Purchased column, for that cells, we found the date-time. No dates for empty adjacent cells.

7. Update Date-Time Upon Changes in Adjacent Cell

We can build a formula that will insert and update dates (and times) whenever we update the adjacent cell.

We will use ADDRESS, CELL functions to create the formula. ADDRESS returns the address for a cell based on a given row and column number and CELL returns information about a cell in a worksheet.

To know more about these functions, visit these Microsoft Support site articles: ADDRESS, CELL.

Our formula will be the following one

=IF(B6<>"",IF(AND(C6<>"",CELL("address")=ADDRESS(ROW(B6),COLUMN(B6))),NOW(),IF(CELL("address")<>ADDRESS(ROW(B6),COLUMN(B6)),C6,NOW())),"")

Formula result for insert dates and auto update in adjacent cells

This formula uses the CELL function to get the reference of the last edited cell, and if it’s the same as the one to the left of it, it updates the date-time value.

Let’s change the value from B4 cell.

Update adjacent cell update date & time

We didn’t change the date, but you can see the change of time.

Drag the formula for a couple of rows.

Drag formula to another cells

Let’s populate the adjacent cells (Input column), you will find the date-time value.

Populate data to insert dates

Conclusion

That’s all for today. We have listed several methods to insert date in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other methods that we have missed here.


Further Readings

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo