If you are looking for Excel automatically enter date when data is entered, then you are in the right place. Microsoft Excel provides a wide range of fruitful ways to show dates along with the timestamps automatically when you enter data. In this article we’ll try to discuss automatically enter date when data is entered in Excel with proper illustrations.
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.
5 Ways to Enter Date Automatically When Data Entered in Excel
Excel offers different ways to automatically enter date when we enter data. We just need to follow the simple steps of each method.
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. Utilizing TODAY Function
Excel has a default TODAY function to input today’s date.
- Like in the picture below, firstly, type the formula in the C4 cell like this.
- Secondly, press ENTER.
- Eventually, you’ll get today’s date as output.
Additionally, you can practice it in the C6 cell.
3. Applying NOW Function
The NOW function adds a timestamp along with the date.
- To show this, firstly, write the formula in the C4
- Secondly, press ENTER and you’ll get both time and date of now as output.
4. Combining IF and 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?
Firstly, select cell C5 and write the formula like this.
Short Explanation of the Functions:
This is the base formula for the Timestamp function. What’s happening here is we’re commanding Excel that If Cell B5 remains empty, Cell C5 will be empty too. And when input data will be entered in Cell B5, then Cell C5 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 is entered.
- Secondly, point your mouse cursor onto the right bottom corner of Cell C5 & you’ll see a ‘+’ icon there which is called Fill Handle.
- Thirdly, Click on it and drag it down to the last cell you need for data entry in Column C & release the mouse button.
- Fourthly, go to the File
- Now, choose Excel Options.
- Fifthly, 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 C may need to refer to itself in the function during data entry in Column B 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.
- Sixthly, enter a name in Cell B5 & press ENTER.
- You’ll see the date & timestamp right away in Cell C5.
- In Cell B6, put another name & the perspective result will be shown right to it in Cell C6.
Thus you can enter any name or data in Column B and you’ll get to know the date & timestamps next to them.
- How to Display Day of Week from Date in Excel (8 Ways)
- Insert Last Saved Date in Excel (4 Examples)
- How to Insert Drop Down Calendar in Excel (With Quick Steps)
- Insert Date in Footer in Excel (3 Ways)
- How to Insert a Date Picker in Excel (With Step-by-Step Procedure)
5. 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.
- Press ALT+F11 & VBA window will appear like in the picture below. Or you can do it manually. For this, firstly, go to the Developer tab > select Visual Basic.
- Secondly, choose Insert > select Module.
- Eventually, a blank module will appear.
- Thirdly, put the following code in the 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.
- 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.
- Put a name in Cell B5.
- Press ENTER & you’re done.
- You’ll get the Entry Time instantly in Cell C5.
Alternatives to Enter Date with Automatic Way
Excel offers some alternatives to enter dates automatically. Those are.
- Using AutoFill
- Utilizing the Fill Series command
1. Using Autofill Option with Multiple Criteria
If you need to input dates according to chronological order then the Autofill option will suit you best. In the picture below, you have to use the Fill Handle in Cell B5 to drag it to B12. From the drop-down in the corner, you’ll find multiple criteria.
Secondly, select Fill Days
Eventually, you’ll find days automatically.
If you choose Fill Weekdays, then the dates will be shown in chronological order except for weekends (Saturday & Sunday).
Consequently, the output is like this.
You can also see months only in a progressive order if you select Fill Months.
In this case, the output is like this.
Additionally, the same way, go for Fill Years to see years in sequential order.
Eventually, here the output will be like this.
2. Utilizing Fill Series Command to Customize Autofill Option
You can use the Fill Series option too if you need to customize dates more including intervals.
- Firstly, select the whole column or a range of cells in a column where you want to input dates.
- Secondly, under the Home tab, go to the Editing group of commands.
- Thirdly, from the Fill drop-down, choose the Series
Eventually, a box will appear to let you customize dates according to your criteria.
- 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 a common difference in an arithmetic progression or series.
- Press OK.
Consequently, this is the resultant series of dates with 2 days of common difference.
Now if you choose Weekday as the Date Unit from the Series box, then the dates will exclude the weekends (Saturday & Sunday).
Eventually, this time you’ll get this.
Select Month as the Date Unit and you’ll see a series of months with 2 months as a common difference or interval between 2 months.
So, the output will be.
Similarly, you can do this for years too by selecting Year as Date Unit.
Consequently, you’ll get the output like this.
So, these are all basic, easy & useful techniques you can follow to make Excel enter dates as well as timestamps automatically when data is entered. 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.
- How to Insert Date in Excel (7 Simple Methods)
- Combine Date and Time in One Cell in Excel (4 Methods)
- How to Change Dates Automatically Using Formula in Excel
- Insert Dates in Excel Automatically (3 Simple Tricks)
- How to auto-populate date in Excel when a cell is updated
- Excel Macro: Insert Date and Time in a Cell (4 Examples)