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

Get FREE Advanced Excel Exercises with Solutions!

One of the most common tasks in Excel is working with dates. In many cases, you may want to automatically enter date when data entered in excel into a particular cell.

Automatically entering the date will reduce the possibility of errors and save you time as well.

In this article, we will explore different methods for Excel to automatically enter date when data is entered in a particular cell, including using features, formulas, and macros.

By the end of this article, you will have a better understanding of how to automate the date entry process in Excel and improve your productivity.

Excel automatically enter date when data entered


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

In this section, we will discuss how Excel automatically enters the dates when data is entered in a particular cell, following 2 simple and effective ways.

We will enter some data (i.e. name) consecutively in an Excel sheet and with every entry of data, you will find the corresponding current date entered into the specific cell of the worksheet.


1. Using IF and NOW Functions to Enter Date Automatically (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 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.

How shall we do this? We will combine the IF function with the NOW function for executing this task.

  • 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. What’s happening here is 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.

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

Enter IF-NOW formula for range of cells

  • Choose Options from the menu.

File Menu list

  • From the Excel Options window, select the Formulas tab & mark the Enable Iterative Calculation >> Click 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.

Enable iterative calculation in Excel options

  • Now, insert a name in Cell B5 & press ENTER.
  • You’ll see the date & timestamp right away in Cell C5.

Excel automatically enter date when data entered

  • In Cell B6, put another name and the prospective result will be shown right to it in Cell C6.

Date entered automatically when enter data

Thus you can enter any name or data in Column B and you’ll get to know the date & timestamps next to them.


2. Customize Excel Function by Embedding VBA and Enter Date Automatically

In Excel, you can use VBA code to perform enormous operations for its multidimensional characteristics. You can use the VBA code to enter the date automatically with every entry of data.

First, you have to create your own customized function by formatting it with VBA code. 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 on the screen. Or you can do it manually: go to the Developer tab >> select Visual Basic.

Visual Basic from Developer tab

  • Click Insert >> select Module.

Insert Module

  • Insert the VBA code in the appeared 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

VBA code to enter date automatically

  • Select Cell C5 & type the following formula as EntryTime is the new function we’ve just built with VBScript.
=EntryTime(B5)
  • Use the Fill Handle once again to copy the formula up to Cell C10 or more according to your need.
  • Now, you will see the Entry Time in cell C5 as soon as you enter data in cell B5.

Enter date automatically with custom function made from VBA

Read More: How to Insert Date in Excel Formula


More Ways to Insert Date and Time Automatically in Excel

In the previous section, we have figured out the process to get the time with every individual entry of data. Let’s check out some more ways of how Excel automatically enters a date when data is entered in a cell of the worksheet.


1. Using Autofill Feature with Multiple Criteria (Days, Weekdays, Months or Years)

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


2. Applying Fill Series Command to Customize Autofill Option

You can use the Fill Series option too if you need to customize dates more including intervals.

  • Select the range of cells >> go to the Home tab >> select Fill under Editing group >> click Series.

Fill Series command from Home tab

Here, the Series box will appear to let you customize dates according to your criteria.

  • Select Series in as Columns, Type as Date & Date unit as Day.
  • Type ‘2’ as the Step Value (common difference in an arithmetic progression or series)
  • Click OK.

Series dialog box

As we have used Step Value as 2 for getting Date as Day, Excel will show output as the progression of days with an interval of 2 like below.

Fill date by an interval of 2 days

Changing the Date Unit section of the Series box to Weekdays, Months, and Series will get your output just like the previous Method.

Read More: How to Insert Current Date in Excel


3. Use the Keyboard Shortcut to Enter the Instant Time and Date

To get today’s date & current timestamp we can use keyboard shortcuts directly.

In any cell where you want to know:

  • 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 + ;) 1st, then SPACE & finally (CTRL + SHIFT + ;).

Keyboard shortcut to enter date and time automatically in Excel


4. Applying the TODAY Function to Enter Today’s Date

Excel has a default TODAY function to input today’s date.

Consider the image below where we have used a dataset of order date and the delivery date of some customers where possible delivery is assumed as today’s date.

Just select a cell where you want to get the date of today and enter the following formula.

=TODAY()

You will get the date of the day you entered.

Excel TODAY function to enter date automatically


5. Use the NOW Function to Enter the Current Time and Date

The NOW function adds a timestamp along with the date.

For the dataset in the image below, we have applied the NOW function in the merged cell D5.

=NOW()

That formula returns both the current date and time.

Excel NOW function to enter date automatically

Read More: How to Insert Dates in Excel Automatically


Frequently Asked Questions

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

2. Can Excel automatically update Data?

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


Takeaways from This Article

  • Automatically updating of the date can be helpful for keeping track of when data was last updated or entered.
  • The smartest approach seems to use the TODAY function combined with the IF function.
  • You can also customize the date format and location within the cell.
  • Automatic updates of the dates can save time and reduce errors by ensuring that the date is always entered accurately and consistently.
  • You should also be aware of potential issues that may arise, such as the date not updating properly if the file is saved and reopened on a different day.

Download Practice Workbook

Download the workbook here that we’ve used to prepare this article.


Conclusion

So, in conclusion, we can say that Excel can automatically enter a date when data is entered in a cell. This feature can be a useful tool for ensuring that important data is always accompanied by the date it was entered or last updated. However, it’s important to use this feature appropriately and be aware of any potential issues that may arise. When used correctly, this feature can save time and reduce errors, making it a valuable tool for anyone working with Excel.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

11 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 Avatar photo
      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 Avatar photo
      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 Avatar photo
      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.

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo