How to Change Dates Automatically Using Formula in Excel

For easing your task you may demand automatic update of date, time sequence number, etc. Excel provides you with a feature for that. Today we are going to show you how to change dates automatically through formulas. For this session, we are using Excel 2019, feel free to use yours.

Here you can see our Output section holds a couple of date or date-related values. Through formula, we have generated these automatically updateable values. In the following sections, we will see the methods.

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 following link.

Change Date Automatically

There are several approaches to change dates automatically. Let’s explore.

1. Classic Formulas using Excel Functions

Excel provides various built-in functions that we can use to make date automatically changeable.

I. TODAY Function

We can use the TODAY function to change dates automatically.  TODAY returns the current date. To know more about the function, visit the TODAY function.

Here we will find today’s date. All we need to do is use the TODAY function.

The formula is as simple as it is

We are creating this tutorial on September 27, 2021. So, it’s the result of the formula. To observe the change, let’s update the date.

Read More: How to Insert Dates in Excel Automatically (3 Simple Tricks)

We have set the date as 29 September. Now you will find the change of date. And it will be an automatic one.

II. NOW Function

Another function that provides the current date is NOW. Moreover, it returns the time value also.

Check this NOW article for further information.

For the time being, we will work only with the date value.

The formula will be the following one

It provides the current date. Let’s change the date for the device to 30 September.

We will find the date has been changed automatically.

2. Complex Formula Combinations to Automatically Change Dates

In the earlier section, we have seen how to use the simple Excel functions to change dates automatically.

Here let’s build a little complex formula. We will use the DATE function to create the formula. To know about this function, visit this article: DATE.

Along with this function, we need to use YEAR, MONTH and DAY functions. To know about the functions visit the articles: YEAR, MONTH, DAY.

Let’s explore the formula

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.

Change the date

You will find the date has been changed automatically.

Similarly, we can write the formula using NOW 

Note one thing that, after changing the date you may need to refresh the Excel application to visualize changes.

3. Change Date Based On Adjacent Cell

I. Update upon Adjacent cell has value

We can build formulas in such a way that for changing the adjacent cell the date & time.

Let’s form a formula that will update the date & time when the adjacent cell is switched from empty to non-empty.

We will use the IF function to execute condition-based time updates. Read this IF article to know about this.

The formula will be the following one

We have checked whether B4 is empty or not, if empty, then checked whether C4 is empty or not. For being empty we set NOW for the cell.

Drag the formula to the next cell.

So far B5 is empty, so the resultant cell is also empty. Let’s insert any random value to B5, and you see the time.

Read More: How to auto populate date in Excel when cell is updated

II. Update Every time an Adjacent cell gets changed

In the earlier section, to change the date-time we needed to make the cell adjacent empty first to then reenter a value. It doesn’t update when you change the contents of the adjacent cell.

If you want the value to update every time the adjacent cell is updated, then we may need to use the following formula

ADDRESS returns the address for a cell based on a given row and column number.

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.

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

Drag the formula for a couple of rows.

Change the B6 value and you will find the updated time.

4. Figure out Date Differences Automatically

We can find the difference between dates automatically. For example, let’s say a HR wants to find the duration of the employees.

To find the difference, we will use the DATEDIF function. Check this DATEDIF article to find information regarding it.

The difference will be counted from the current date. So, we will use TODAY.

The formula will be the following one

We have used a couple of DATEDIF that find the difference between the Joining Date and TODAY. First DATEDIF calculates the difference in month format (as we have used “m”) and the second one calculates the difference in day format.

Let’s exercise the AutoFill feature.

We found the difference for all the employees. Now let’s change the date.

You will find the differences have been changed automatically.

Conclusion

That’s all for today. We have listed several approaches to change dates automatically. 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

• How to Combine Date and Time in One Cell in Excel (4 Methods)
• How to Insert Date in Excel (7 Simple Methods)
• Excel Automatically Enter Date When Data Entered (7 Easy Methods)