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
=TODAY()
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
=NOW()
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
=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))
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
=DATE(YEAR(NOW()),MONTH(NOW()),DAY(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
=IF(B4<>"",IF(C4<>"",C4,NOW()),"")
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
=IF(B6<>"",IF(AND(C6<>"",CELL("address")=ADDRESS(ROW(B6),COLUMN(B6))),NOW(),IF(CELL("address")<>ADDRESS(ROW(B6),COLUMN(B6)),C6,NOW())),"")
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
=DATEDIF(C4,TODAY(),"m")&" months " &DATEDIF(C4,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.
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.