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.
Comments
Post a Comment