Excel Automatically Enter Date When Data Entered (7 Easy Methods)

Microsoft Excel provides a wide range of fruitful ways to show dates along with the timestamps automatically when you enter data. Here I’ll try to incorporate all of those simple & basic methods of automatically enter date when data entered 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.


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.

Enter date in excel automatically when data entered keyboard shortcut


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.

Automatically enter date by using today function


3.   Using the NOW Function

The NOW function adds a timestamp along with the date like the picture below.

Automatically enter date and timestamp by using now function


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.

automatically enter date and timestamps by using if and now functions

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.

automatically enter date and timestamps by using if and now functions

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.

automatically enter date and timestamps by using if and now functions

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.

automatically enter date and timestamps by using if and now functions

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.

automatically enter date and timestamps by using if and now functions

Step 4:

➤ Now enter a name in Cell A2 & press Enter.

You’ll see the date & timestamp right away in Cell B2.

automatically enter date and timestamps by using if and now functions

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.

automatically enter date and timestamps by using if and now functions


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.

Automatically enter date by using autofill

If you choose Fill Weekdays, then the dates will be shown in a chronological order except weekends(Saturday & Sunday).

Automatically enter date by using autofill

You can also see months only in a progressive order if you select Fill Months.

Automatically enter date by using autofill

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.

Automatically enter date by using fill series

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.

Automatically enter date by using fill series

Now this is the resultant series of dates with 2 days of common difference.

Automatically enter date by using fill series

Now if you choose Weekday as Date Unit from the Series box, then the dates will exclude the weekends(Saturday & Sunday).

Automatically enter date by using fill series

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.

Automatically enter date by using fill series

Similarly, you can do this for years too by selecting Year as Date Unit.

Automatically enter date by using fill series


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.

Automatically enter dates when data entered using VBA function or 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.

Automatically enter dates when data entered using VBA function or coding

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.

Automatically enter dates when data entered using VBA function or coding

Step 4:

➤ Put a name in Cell B5.

➤ Press Enter & you’re done.

You’ll get the Entry Time instantly in Cell C5.

Automatically enter dates when data entered using VBA function or coding


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.


Further Readings

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

2 Comments
  1. In case 4, is necessary enable iterative calculation? What difference with just write “=IF(A2″”,NOW(),””)” ?

    • Hi Roberto,
      If you write “=IF(A2“”,NOW(),””)” in Cell B2, then the timestamp in Cell B2 will update for each input name in the following cells(A3,A4,…….). To fix timestamp for each entry, you have to type “=IF(A2””,IF(B2=””,NOW(),B2),””)” in Cell B2.

Leave a reply

ExcelDemy
Logo