Automatically Enter Date When Data Entered in Excel (2 Ways)

We’ll use the following sample dataset to showcase how you can enter the editing time in a cell.

Automatically Enter Date When Data Entered in Excel: 2 Easy Ways

We will enter some data (i.e. name) in an Excel sheet. Whenever we do, we’ll get the time of the edit in a cell next to it.

Method 1 – Using IF and NOW Functions to Enter Date Automatically (Timestamps)

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 (column B) every day. Another column next to it will show automatically their entry timestamps along with the dates when they enter their names in the first column.

Select cell C5 and enter the formula below:

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

Formula Explanation

This is the base formula for the Timestamp function. If Cell B5 remains empty, Cell C5 will be empty, too. When something is entered in Cell B5, the Cell C5 will get a timestamp. This works because Excel will automatically recalculate all cells whenever one is changed.

Drag the Fill Handle down to the last cell in Column C (the column specified for entry of data).
Go to the File tab.

Choose Options from the menu.

From the Excel Options window, select the Formulas tab and mark the Enable Iterative Calculation.
Click OK.

Cells in Column C need to refer to itself in the function during data entry in Column B to execute the function. If we don’t enable iterative calculation from Excel Options, an error message prompt will be shown during data entry.

Insert a name in Cell B5 and press Enter.
You’ll see the date and timestamp in cell C5.

In Cell B6, put another name and the respective result will be shown in Cell C6.

Method 2 – Customize an Excel Function by Embedding VBA and Enter Date Automatically

Press Alt + F11 and a VBA window will appear. Alternatively, go to the Developer tab and select Visual Basic.

Click Insert and select Module.

Insert the VBA code in the new Module window.

Code:

Function EntryTime(LeftCell As Range)
   If LeftCell.Value <> "" Then
   EntryTime = Format(Now, "dd-mm-yy hh:mm:ss")
   Else
   EntryTime = ""
   End If
End Function

Run the code and save the file.
Select Cell C5 and use the following formula in it:

=EntryTime(B5)

Use the Fill Handle to copy the formula down to fill the column.
You will see the Entry Time in cell C5 as soon as you enter data in cell B5.

More Ways to Insert Date and Time Automatically in Excel

Case 1 – Using the Autofill Feature with Multiple Criteria (Days, Weekdays, Months, or Years)

If you need to input dates in chronological order, then the Autofill option will suit you best. In the picture below, you have to use the Fill Handle in Cell C5 to drag it to C15. From the drop-down in the corner, you’ll find multiple criteria like

Fill Days
Fill Weekdays
Fill Months
Fill Years

Select the option you need and get the corresponding result.

Case 2 – Applying the Fill Series Command to Customize Autofill Option

Select the range of cells.
Go to the Home tab.
Select Fill under Editing group.
Click Series.

The Series box will appear.
Select Series in as Columns, Type as Date and Date unit as Day.
Type 2 as the Step Value (difference between terms in an arithmetic progression or series).
Click OK.

Excel will show an output as the progression of days with an interval of 2 like below.

Case 3 – Use the Keyboard Shortcut to Enter the Time and Date

For today’s date, press Control + Semi-colon (CTRL + ;).
Use (Ctrl + Shift+ ;) to enter the current time automatically.
If you want to enter both in a cell, then press (Ctrl + ;), then press Space, then press (Ctrl + Shift + ;).

Case 4 – Applying the TODAY Function to Enter Today’s Date

Select a cell where you want to get today’s date and enter the following formula.

=TODAY()

Case 5 – Use the NOW Function to Enter the Current Time and Date

We have applied the NOW function in the merged cell D5.

=NOW()

That formula returns both the current date and time.

Frequently Asked Questions

How do I stop Excel from automatically entering the date when data is entered?

Use a space or apostrophe before you enter your date or number. The space and the apostrophe are not visible in the cell after pressing Enter.

Can Excel automatically update Data?

To keep the external imported data updated, refresh the data in the worksheet.

Download the Practice Workbook

Automatically Enter Date When Data Entered.xlsm

<< Go Back to Insert Date | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

11 Comments

Roberto Villamil Jun 27, 2021 at 7:30 AM

In case 4, is necessary enable iterative calculation? What difference with just write “=IF(A2″”,NOW(),””)” ?
- Reply
  Nehad Ulfat Jun 30, 2021 at 11:01 PM
  
  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.
shanmugam Jun 30, 2022 at 9:59 PM

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!
Yingyang Sep 3, 2022 at 1:37 AM

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!
Jay Sep 27, 2022 at 10:41 PM

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?
- Reply
  Tanjim Reza Sep 28, 2022 at 4:50 PM
  
  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
Jackie Nov 29, 2022 at 4:33 AM

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.
Adv Asadusjaman Dec 24, 2023 at 9:37 PM

I’m trying to create a case diary for our law firm. I have an excel spreadsheet of daily case list which contains name of the courts, parties, steps, and next date of hearing. What I want is that when the next date of a particular case will be inputted, particulars of that case will automatically be copied over to the cells where that new date is located. So that I don’t have to write the same data with the new date.

For example, in my spreadsheet collum A contains ”Date”, collum B contains “Previous Date”, collum C contains ”Name of the court”, D contains ”Litigation No.”, E & F contain name of the parties, J contains ”Next Date”. Say, I have all the information written through cell A2 to cell I2. Once I input the next date in cell J2, i,e., 15/01/2024, I want excel to automatically copy the new date of J2 in B15 as “Previous Date” and copy data of cell B2 to cell I2 in cell B15 to I15 (cells A15 to J15 are specified for 15/01/2024). This link contains a sample spreadsheet https://docs.google.com/spreadsheets/d/1mw-_xE8D9i4maD508k67rsCNB9GaTzdT/edit?usp=drive_link&ouid=112249631105588064390&rtpof=true&sd=true