Download Practice Workbook
Download the workbook here that we’ve used to prepare this article. You can use it as a calculator too as we’ve embedded the output cells with formulas.
7 Easy Techniques to Make Excel Automatically Enter Date When Data Entered
1. Using Keyboard Shortcuts
To get today’s date & current timestamp we can use keyboard shortcuts directly.
In any cell where you want to know today’s date, press CTRL + ; (Control + Semi-colon).
Use CTRL + Shift + ; to enter the current time automatically.
If you want to enter both in a cell, then press CTRL + ; 1st, then SPACE & finally CTRL + SHIFT + ;. You’ll get the date & timestamp together.
2. Using the TODAY Function
Excel has a default Function to input today’s date.
Like in the picture below, type =TODAY() in a cell, press Enter & you’ll see today’s date there at once.
3. Using the NOW Function
The NOW function adds a timestamp along with the date like the picture below.
4. Combining IF & NOW Functions (Timestamps)
Now, let’s assume that we want the entry time of each employee in an office & the employees will input their entrance timestamps by entering their names only in a spreadsheet column everyday. Another column next to it will show automatically their entry timestamps along with the dates when they enter their names in the first column.
How shall we do this?
Step 1:
➤ Select Cell B2 & type =IF(A2””,IF(B2=””,NOW(),B2),””)
🔎 Short Explanation of the Function:
This is the base formula for the Timestamp function. What’s happening here is we’re commanding Excel that If Cell A2 remains empty, the Cell B2 will be empty too. And when an input data will be entered in Cell A2, then Cell B2 will show the timestamp at once. The whole thing will be executed by the combination of two simple functions- IF & NOW. We’ll use IF to enter the condition & NOW function will show the time when data entered.
Step 2:
➤ Point your mouse cursor onto the right bottom corner of the Cell B2 & you’ll see a ‘+’ icon there which is called Fill Handle.
➤ Click on it and drag down to the last cell you need for data entry in Column B & release the mouse button.
So now the whole Column B is prepared for showing timestamps along with the dates as you have just copied the entire column by using Fill Handle.
Step 3:
➤ Now go to File ribbon & choose Excel Options.
➤ Select the Formulas tab & mark the Enable Iterative Calculation.
➤ Click on OK.
🔎 What we’re doing here is telling Excel that any cell in Column B may need to refer itself in the function during data entry in Column A to execute the function. And if we don’t enable this iterative calculation from Excel options then an error message prompt will be shown during data entry.
Step 4:
➤ Now enter a name in Cell A2 & press Enter.
You’ll see the date & timestamp right away in Cell B2.
In Cell A3, put another name & the perspective result will be shown right to it in Cell B3.
Thus you can enter any name or data in Column A and you’ll get to know the date & timestamps next to them.
5. Using Autofill Option with Multiple Criteria
If you need to input dates according to the chronological order then the Autofill option will suit best. In the picture below, you have to use Fill Handle in Cell B4 to drag it to B13. From the drop-down in the corner, you’ll find multiple criteria.
If you choose Fill Weekdays, then the dates will be shown in a chronological order except weekends(Saturday & Sunday).
You can also see months only in a progressive order if you select Fill Months.
And the same way, go for Fill Years to see years in sequential order.
6. Using Fill Series Command to Customize Autofill Option
You can use the Fill Series option too if you need to customize dates more including intervals.
Step 1:
➤ Select the whole column or a range of cells in a column where you want to input dates.
➤ Under the Home tab, select the Editing group of commands.
➤ From the Fill drop-down, choose the Series option.
A box will appear to let you customize dates according to your criteria.
Step 2:
➤ In the Series box, select Series in as Columns, Type as Date & Date unit as Day.
➤ Type ‘2’ as the Step Value, this is called common difference in an arithmetic progression or series.
➤ Press Enter.
Now this is the resultant series of dates with 2 days of common difference.
Now if you choose Weekday as Date Unit from the Series box, then the dates will exclude the weekends(Saturday & Sunday).
Select Month as Date Unit and you’ll be shown a series of months with 2 months as common difference or interval between 2 months.
Similarly, you can do this for years too by selecting Year as Date Unit.
7. Embedding VBA Commands to Make Excel Function
And now here’s the final method where you can use your own customized function by formatting it with VBA coding first. We’re going to know the Entry Time of the employees once again here but this time with our own function.
Step 1:
➤ Press Alt+F11 & VBA window will appear like in the picture below.
➤ From the Insert tab, select Module command & now you’re ready to make your own formula by coding.
Step 2:
➤ Copy the following codes & paste them into your code module.
Function EntryTime(LeftCell As Range)
If LeftCell.Value “” Then
EntryTime = Format(Now, “dd-mm-yy hh:mm:ss”)
Else
EntryTime = “”
End If
End Function
➤ Now press Alt+F11 again to close the VBA window or just return to your Excel datasheet.
Step 3:
➤ Select Cell C5 & type =EntryTime(B5) as EntryTime is the new function we’ve just built with VBScript.
➤ Use Fill Handle once again to copy the formula up to Cell C10 or more according to your need.
Step 4:
➤ Put a name in Cell B5.
➤ Press Enter & you’re done.
You’ll be displayed the Entry Time instantly in Cell C5.
Conclusion
So these are all basic, easy & useful techniques you can follow to make Excel enter dates as well as timestamps automatically when data entered. I hope, you’ve found these methods quite effective. If you got any questions or feedback regarding this article you can comment. You can also have a glance at our other interesting & informative articles on this website.
Related Articles
Date & Time in Excel – How to Enter Them in Worksheet Cells Effectively?
Excel Pivot Table Auto Grouping by Date, Time, Month, and Range!
Excel Conditional Formatting Dates Older than Today
Number of Days Between Two Dates Calculator
The post Excel Automatically Enter Date When Data Entered appeared first on ExcelDemy.
