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.

Overview - Excel Automatic Date Change Formula

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.

Find Today's date - Excel Automatic Date Change Formula

The formula is as simple as it is

=TODAY()

Today's date - TODAY - Excel Automatic Date Change Formula

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)

Change date - Excel Automatic Date Change Formula

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

Updated date - Excel Automatic Date Change Formula

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

=NOW()

Today's date - NOW - Excel Automatic Date Change Formula

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

Change of Date - Excel Automatic Date Change Formula

We will find the date has been changed automatically.

Updated dates - Excel Automatic Date Change Formula

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

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

Complex formula - Excel Automatic Date Change 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.

Date with complex formula - Excel Automatic Date Change Formula

Change the date

Change of Date - Excel Automatic Date Change Formula

You will find the date has been changed automatically.

Updated date - complex - Excel Automatic Date Change Formula

Similarly, we can write the formula using NOW 

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

Complex formula - NOW - Excel Automatic Date Change Formula

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

=IF(B4<>"",IF(C4<>"",C4,NOW()),"")

Adjacent cell date formula - Excel Automatic Date Change Formula

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.

Drag down to cell - Excel Automatic Date Change Formula

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.

Change cell value - Excel Automatic Date Change Formula

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

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

Adjacent cell address formula - Excel Automatic Date Change 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.

Update cell update time - Excel Automatic Date Change Formula

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

Drag the formula for a couple of rows.

Drag down to next rows - Excel Automatic Date Change Formula

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

Update cell update dates - Excel Automatic Date Change Formula

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.

Duration finding data - Excel Automatic Date Change Formula

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

=DATEDIF(C4,TODAY(),"m")&" months " &DATEDIF(C4,TODAY(),"md")&" days")

Difference finding formula result - Excel Automatic Date Change Formula

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.

AutoFill - Excel Automatic Date Change Formula

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

Change of Date - Excel Automatic Date Change Formula

You will find the differences have been changed automatically.

Updated difference - Excel Automatic Date Change Formula

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

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