How to Perform Automatic Date Change in Excel Using Formula (5 Useful Methods)

Today we are going to show some automatic date change formulas in Excel, in other words, how to change dates automatically through formulas in Excel.  In many situations, to ease your task, you may demand automatic updates of date, time sequence number, etc. Excel provides you with a feature for that. For this session, we are using Excel 2019, feel free to use yours.

Excel Automatic Date Change Formula: 5 Useful Methods

In this section, I will discuss 5 helpful methods to change dates automatically with formulas in Excel. But before jumping into those methods, let’s understand the context first. In a real-life situation, you may demand an automatic update of the date, time sequence number, etc. to ease your task. Below, I have given two examples where on one occasion, we need an automatically updatable present date and on another occasion, we need to calculate the times remaining for the next Christmas.

As the day changes, the output section will be updated automatically. To know how we can do that, let’s explore our 1st method.

1. Using Formula with Excel TODAY Function to Change Date Automatically

We can use the TODAY function to change dates automatically. TODAY returns the current date. To know more follow the steps below.

Steps:

• In the below example, we will insert Today’s date in cell C5 which will be updated automatically in the future as the date gets changed.

• To do that, write down the following formula in cell C5 and press Enter.

• As a result, we will see the current date. As I am writing this article on 5th December 2022, it is showing 5-Dec-22.

• To verify if this date will be updated in the future or not, we can manually change the current date on the computer system and observe. For this, we are changing the date from 5th December 2022 to 14th December 2022 on my computer. To do that, go to Setting> Time & Language > Set the date and time manually> Change.

• Now, set the desired date and time and click Change.

• Now if we reload the Excel file again, we will see that the date is showing 14-Dec-22.

Read More: How to Insert Date in Excel Formula

2. Use of NOW Function for Automatic Change of Date

Another function that provides the current date is the NOW function. Moreover, it returns the time value. To apply the formula, follow the steps below.

Steps:

• Like the previous method, on cell C5, write down the following formula and press Enter.

• Now, you will see that this function is also giving the current date.

• As the NOW function returns a dynamic value, it will also get updated automatically like the TODAY function.

3. Applying Complex Formula Combination

In the earlier section, we have seen how to use simple Excel functions to change dates automatically.  Here let’s build a little complex formula. This time,  we will use a combination of DATE, YEAR, MONTH, DAY, and TODAY functions to create a formula that will automatically change the result. To apply the formula, follow the steps below.

Steps:

• In cell C5, write down the following formula and press Enter.

• Consequently, you will get Today’s date as a result.

• As this formula has the dynamic function TODAY, the result will also get automatically updated.

Note: Instead of the TODAY function, we can also use the NOW function to get a similar result.

Read More: How to Insert Current Date in Excel

4. Change of Date Based On Adjacent Cell Value

We can build formulas in such a way that  Excel will update the resultant date for any change in the adjacent cell. Here we will see two examples related to this scenario.

4.1 Auto Updation of Date upon Data Entry of Adjacent Cell

In this example, we have an Input column and a Date of 1st Data Entry column. For now, the Input column cells are empty. We want the date when these cells become non-empty or in other words when we enter the values into those cells .

Here, we will use the IF function to execute condition-based time updates. To know more, follow the steps below.

Steps:

• In cell C5, write down the following formula and then press Enter.

• Then use Fill Handle to auto-fill the cell C6.
• Now, if we enter something on cells B5 and B6,  Excel will automatically display the current date on cells C5 and C6 respectively.

• After that, even if you further change the values in B5/B6, the Dates on C5/C6 will be unchanged as it is not the 1st time you are entering the value.

4.2 Auto Updation of Date When Adjacent Cell Value 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 a new formula. To know more, follow the steps below.

Steps:

• In cell C5, write down the following formula and press Enter.

• Use the Fill handle to autofill C6.
• Now if I enter anything on cell B5, the entering date will be shown on cell C5.

• Now, if we change the input, the Date value will also change.

Please note that in both of the examples, if you find any error while applying the formula you should check whether Iterative calculation is enabled or not. To do that, Go to File > Options > Formulas. Then check the Enable Iterative Calculation and set the Maximum Iteration to 1.

Read More: How to Insert Dates in Excel Automatically

5. Calculation of Date Difference Automatically

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

To find the difference, we will use the DATEDIF function.  Here, Excel will need to count the difference from the current date. So, we will use the TODAY function. To apply the formula, follow the steps below.

Steps:

• On cell D5, write down the following formula and click Enter.

• As a result, we will have the duration of the employee’s service.

• Now, if you use Fill Handle to Auto-fill the rest of the cell, you will get the duration of each employee.

• As this formula has the dynamic function TODAY in it, Excel will update the date automatically.

Thus you can perform an automatic change of date using the Excel formula.

Things to Remember

• If you do not find the result in the proper date format, you can change the formatting of the cell and set the date format you wish to display.

Download Practice Workbook

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

Conclusion

That’s all for today. We have listed several approaches regarding Excel’s automatic date change formula. 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

• Automatically Enter Date When Data Entered in Excel
• How to Auto Populate Date in Excel When Cell Is Updated
• How to Insert Day and Date in Excel
• How to Get the Current Date in VBA

<< Go Back to Insert Date | Date-Time in Excel | Learn Excel