Today we are going to show some automatic date change formula in Excel, in other words, how to change dates automatically through formulas in Excel. In many situations, for easing 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 for easing 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.
=TODAY()- 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 Dates in Excel Automatically
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 also. 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()- 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.
Read More: How to Insert Static Date in Excel
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.
=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())) - 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.
🎓 How Does the Formula Work?
The YEAR function fetches the year value from the result of TODAY. And the MONTH and DAY functions fetch the month and day values respectively from the result of TODAY. Then the DATE function returns the date.
Note: Instead of the TODAY function, we can also use the NOW function to get a similar result.
Read More: How to Auto Populate Date in Excel When Cell Is Updated
Similar Readings
- How to Insert Date in Excel Formula
- Insert Last Saved Date in Excel
- How to Display Day of Week from Date in Excel
- Formula for Weekly Dates 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.
=IF(B5<>"",IF(C5<>"",C5,NOW()),"")- 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.
How Does the Formula Work?
- IF(C5<>””,C5,NOW())
C5<>“” implies Is C5 Not Empty. If it is True (Not empty), it will return C5. Otherwise, it will return present time (NOW).
- IF(B5<>””,IF(C5<>””,C5,NOW()),””)
If B5 is not empty, then it will return the Present Date & Time. Otherwise, it will return a blank cell.
Read More: How to Insert Current Date in Excel
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.
=IF(B5<>"",IF(AND(C5<>"",CELL("address")=ADDRESS(ROW(B5),COLUMN(B5))),NOW(),IF(CELL("address")<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW())),"")
How Does the Formula Work?
IF(B5<>"",IF(AND(C5<>"",CELL("address")=ADDRESS(ROW(B5),COLUMN(B5))),NOW(),IF(CELL("address")<>ADDRESS(ROW(B5),COLUMN(B5)),C5,NOW())),"")
The ADDRESS function 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.
- 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: Automatically Enter Date When Data Entered in Excel
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.
=DATEDIF(C5,TODAY(),"m")&" months " &DATEDIF(C5,TODAY(),"md")&" days"- 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.
How Does the Formula Work?
- DATEDIF(C5,TODAY(),”m”)&” months ” &DATEDIF(C5,TODAY(),”md”)&” days”
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.
Thus you can perform automatic change of date using Excel formula.
Read More: How to Remove Time from Date in Excel
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 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.
