Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Automatically Enter Date When Data Entered in Excel

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.

excel automatically enter date when data entered


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.
=TODAY()

Utilizing TODAY Function

  • Secondly, press ENTER.
  • Eventually, you’ll get today’s date as output.

Additionally, you can practice it in the C6 cell.

excel automatically enter date when data entered


3. Applying NOW Function

The NOW function adds a timestamp along with the date.

  • To show this, firstly, write the formula in the C4
=NOW()

Applying NOW Function

  • Secondly, press ENTER and you’ll get both time and date of now as output.

excel automatically enter date when data entered


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?

Step 1:

Firstly, select cell C5 and write the formula like this.

=IF(B5<>"",IF(C5="",NOW(),C5),"")

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.

Combining IF and NOW Functions (Timestamps)

Step 2:

  • 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.

excel automatically enter date when data entered

Step 3:

  • 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.

excel automatically enter date when data entered

Step 4:

  • 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.

excel automatically enter date when data entered

Similar Readings


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.

Step 1:

  • 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.

Embedding VBA Commands to Make Excel Function

  • Secondly, choose Insert > select Module.

excel automatically enter date when data entered

  • 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

 

excel automatically enter date when data entered

  • 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 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.

Using Autofill Option with Multiple Criteria

Secondly, select Fill Days

excel automatically enter date when data entered

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.

excel automatically enter date when data entered

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.

excel automatically enter date when data entered


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.

Step 1:

  • 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

Utilizing Fill Series Command to Customize Autofill Option

Eventually, 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 a common difference in an arithmetic progression or series.
  • Press OK.

excel automatically enter date when data entered

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.

excel automatically enter date when data entered

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.

excel automatically enter date when data entered

Consequently, you’ll get the output like this.

excel automatically enter date when data entered


Conclusion

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.


Related Articles

Nehad Ulfat

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!

10 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.

  2. Haiiii …. i take the one number 30,000 but i need the number as 0.36

    For Example :
    Data is ( Target – 30000 )
    But i need this number like ( 0.36 ) in excel Formula ….. Plz Help

    • Reply
      Osman Goni Ridwan Aug 4, 2022 at 3:11 PM

      Hello SHANMUGAM, can you please explain your problem a bit more elaborately? Then it will be easy to help you. Thanks!

  3. Thank you for the detailed step by step. It helped a lot 🙂

    Can this part of the formula please be elaborated?
    Why do we need to include this?
    IF(B2="",NOW(),B2)
    I want to understand how this works.

    (my understanding.. if B2 is empty, then input date.????)

    • Reply
      Osman Goni Ridwan Sep 4, 2022 at 10:11 AM

      Hello YINGYANG!
      Syntax of IF function is as follows: =IF(logical_test, [value_if_true], [value_if_false])

      So, for the formula =IF(B2="",NOW(),B2):
      >> logical_test – B2=”” : It is the condition of the IF function. The condition is when cell B2 is blank.

      >> value_if_true – NOW() : This is the output when the cell meets the logical test. The NOW() function gives the present time in the cell.

      >> value_if_false – B2 : When the cell will not meet the criteria, the IF function will return the cell value of B2.

      So, in brief, when cell B2 is empty, the formula will insert the present time in the cell; if it isn’t empty, it will leave the cell as it is.

      I hope, your problem will be solved in this way. You can share more problems in an email at [email protected]

      Thank You!

  4. I think your input for IF and NOW combination is incorrect and longer than necessary. I did it the way you had it and it’s a circular formula that didn’t work because it’s referring the itself. I made it work with formula =IF(J3″”,NOW(),””). Will this formula work differently than the one you created? Yours worked once I made it =IF(J3″”,IF(J3″,NOW(),J3),””). On the surface they do the same thing. Am i missing something?

    • Hi Jay!
      Thank you for your query!
      Our formula here is correct.
      Because, if you use =IF(J3<>"",NOW(),"") in Cell K3, and copy it down column K, whenever you write something in column J, the NOW function will return the same timestamp in all cells of column K (K3, K4, K5,….). You will see no difference in the timestamps and will lose the original entry time.
      To fix this for each entry, you have to type =IF(J3<>"",IF(K3="",NOW(),K3),"") in Cell K3, and copy it down the cells of column K. (You have to copy the formula first, even though there is no input in column J yet.)
      This formula is designed in such a way that the NOW function will not return the current time when there is already a timestamp recorded. Look closely, IF(K3="",NOW(),K3) part ensures that the IF function will return what is already in column K cells if there is any priorly.
      Look at the following GIF image for more clarification.

      Regards,
      Tanjim Reza

  5. Hi, when using the If Now formula, instead of inputting today’s date, excel is inputting January 1st, 1900. I assume there’s some issue happening where excel is unable to reference today’s date and is using a default date. Is there a way to fix this issue? Thanks

    • Reply
      Naimul Hasan Arif Nov 30, 2022 at 10:53 AM

      Hello JACKIE,
      Thanks for your comment. If we use these formulae, it will generate the date from that device (PC/Laptop/Mobile). So, check the date of your device first.
      I think it will solve your problem.

Leave a reply

ExcelDemy
Logo