How to Automatically Enter Date & Time After Data Entry In Excel

How to Automatically Enter Date and Time in Excel After Data Entry

In Excel, the ability to automatically insert date in cells is a convenient feature. After making any changes or entering data, it's critical to add the current date and time to an attendance sheet or change tracker. It saves a good chunk of time and keeps track of when you enter or change data in a specific cell and another cell is populated with current date and time. In this Excel tutorial, I'll go over two critical concepts related to Excel's ability to automatically fill in date and time.

Whenever new data is entered in another cell, the current date is automatically entered as well.

If the value in that cell changes, make the appropriate adjustments.

Normally, I'd use VBA to fix issues like this, but that wasn't an option today. To resolve this, I will make use of Excel's built-in functions and formulas. Before you begin, you should have a basic understanding of Excel's Iterative Calculation feature.

Iterative Calculation can give you results by performing a calculation many times until you get the desired outcome. That's also possible in Excel. Using formula, you can direct Excel to perform a calculation a certain number of times and then display the results. I'll need Iterative Calculation in this case because I'm going to calculate two cells at a time and alter one based on the results of the other. A circular reference error is normally displayed, but with iterative calculation enabled, we can run the formula seamlessly. To summarize, iterative calculation is required to automatically insert dates into Excel. Let's see how to make this feature available in Excel now:

Go to the File Tab to access the backstage view.

Click and select “Options.”

Choose “formulas” in step three.

Make sure "Enable Iterative Calculation" is selected in step four before continuing.

Lastly click "OK" and "Done" to complete the process.

Before we can write the formula, we must first format the entire column as a date. You can format the column so that it only shows the date or time or both or any data formatting you want. This is because our formula returns a date serial number, and formatting the column to be a date will change it to a date instead.

If you want to format the entire column, select it, then right-click and choose Format Cells from the menu that appears. After that, click "Custom" and then pick your prefeed timestamp format from the list that appears. Done!

After that is completed, the formula can begin. As previously stated, there are two options for setting an automatic date. Let's take a closer look at both scenarios and figure out how to deal with them.

Data entered in a row or cell will, by default, have the date automatically entered.

As you'll see in the video, we'll be entering data in Column A and entering the date and time in Column B.

Copy the formula and paste it into Cell B1. To copy the formula, click here.

If you make any changes to the cell data, this formula will not automatically update the date or time. Use the second link formula for that, and this is where our second example comes into play.

Here's how things stand at the moment. If we enter anything in cell A1, the date and time will be automatically filled in in cell B1 (depending on your formatting). Cell B1's date and time will be updated immediately if we make changes to cell A1. The second formula in the linked document will help you with this.

Let's now break down the formula in easy terms. For the first scenario, I combined two functions: "If Function" and "NowFunction." Cells B1 and A1 will be checked to see if they are empty before cell B1 is filled with the date and time.

The auto update feature was achieved by combining five more functions in the second formula. That’s how to automatically add the current date and time in Excel. Thank you so much for reading this article. Please help us out by becoming a patron on Patreon.