An automatic system using an excel formula that allows you to populate date and time when data is entered into a different cell, leaving you with endless opportunities in excel.

Auto-date in Excel is a cool feature to have. If you are maintaining an attendance sheet, tracking cell changes or even doing data entry, inserting the current date and time into the cell after any changes or data entry is crucial. It saves tons of time and also keeps track of when you enter data or change data in a certain cell. In this intermediate Excel tutorial, I’ll be explaining two crucial things about auto-filling date and time in Excel.

1. Automatically enter the date when data is entered in another cell.

2. Update the date and time if that cell changes.

Normally, I solve these types of problems with VBA, but not today. I will use Excel's built-in features and formulas to solve this. Before starting, you need a basic idea of a feature called Iterative Calculation in Excel.

Iterative Calculation can provide you with results by calculating something numerous times until your desired result is achieved. In Excel, you can do that too. You can tell Excel to calculate something, say 100 times, and display the result. In our case here, I’ll need Iterative Calculation because I’m going to calculate two cells at a time and change one based on another’s results. Normally it would display a circular reference error, but with iterative calculation enabled, we’ll be able to run the formula. In short, to insert dates in Excel automatically, we need iterative calculation. Now let’s learn how we can enable this feature in excel:

Step 1: Go to the backstage view by clicking on the File Tab

Step 2: Select Options.

Step 3: Select Formulas.

Step 4: Checkmark "Enable Iterative Calculation"

Step 5: Click "OK" and "Done."

Another important thing we need to do before we write the formula is to format the entire column as a date. You can format the column in a way that it only shows date or time or both or any data formatting you like. Because our formula will give us a date serial number, and we need to change that by formatting the column as a date.

You can format the column by selecting the entire column and then right-click & Select Format Cells. Now select "Custom" and select your prefeed timestamp format. Done!

With that done, it is time for the formula to kick in. As I said earlier, there are two solutions here regarding the automatic date. Let’s understand both the scenarios and solve them.

The first scenario is to automatically enter the date when data is entered in a row or cell.

In the video we’ve shown a situation where we enter data in Column A and the date and time will be entered in Column B.

Now copy the formula and paste it in Cell B1. Click on the button below to copy the formula.

This formula will not update the date or time if you change the cell data. For that, you need to use the second formula from the link, and this is where our second scenario kicks in.

The situation is something like this. If we enter any data in cell A1, cell B1 will be automatically populated with the date and time (depending on your formatting). But the important part is, if we edit cell A1, the date and time from cell B1 will be updated instantly. For that, you need to use the second formula from the link.

Now let’s explain the formula in plain words. I’ve combined two functions for the first scenario, "If Function" and "Now Function." I’ve written them in a way so that it will check if cell B1 and A1 are empty or not and then enter date and time on cell B1.

In the second formula, I’ve combined an additional five functions to achieve the auto update feature. Those functions are: AND Function, CELL Function, ADDRESS Function, ROW Function, and COLUMN Function.

This is how we add date and time in Excel automatically. If you are passionate about VBA, here is another tutorial to automatically enter the date when data is entered into a column using VBA. Thanks a million for reading. Please support us by being a patron on Patreon.