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.

**Table of Contents**hide

## 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.