Automatically Enter Date When Data Is Entered in Excel

Sometimes we need to enter a date automatically when data is entered in Excel. In this article, we are going to show you how to enter date automatically when data is entered in Excel in 5 swift approaches. We have also included some relevant concerns at the end of this article. So let’s get started.


📂 Download Excel File

Download the free workbook from here:


Learn to Automatically Enter Date When Data Is Entered in Excel with 5 Easy Approaches

While writing this article, “How to Enter Date Automatically when Data Entered in Excel”. We’ve considered a dataset that contains approximately 10 columns and 4 rows. In this dataset, we added Product, ID, and Manufacturing Date to make you better understand. But if you want, you can change the entities and put the values of your own.

Dataset to how to enter date automatically in Excel when data entered

APPROACH

1. Application of Keyboard Shortcuts to Enter Date Automatically in Excel 

Using Ctrl+Semicolon

One of the easiest ways to enter dates automatically in Excel when data is entered is the application of a keyboard shortcut. You can do this by performing the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Keyboard Shortcut sheet.
  • Then select a cell (we selected D5 cell).
  • Now press Ctrl+;  (Control+Semicolon).

Application of Keyboard shortcuts to how to enter date automatically in Excel when data entered

  • Finally, you will see that the date has been automatically entered in Excel.

Application of keyboard shortcut to how to enter date automatically in excel when data entered

Using Ctrl+Shift+Semicolon

There is another shortcut for keyboard application in Excel when entering data. We are discussing this below for you.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, go to the Keyboard Shortcut sheet.
  • Then select cell D5.
  • Now tap Ctrl+Shift+;  (Control+Shift+Semicolon).

another keyboard shortcut to how to enter date automatically in excel when data entered

  • Lastly, you will see the time entered in that cell.

result of using keyboard shortcut to how to enter date automatically in excel when data entered

Using Multiple Keyboard Shortcuts

There is one more technique of the application of a keyboard shortcut to enter dates automatically when data is entered in Excel. You just need to follow the below steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, select the D5 cell.
  • Then tap Ctrl+; ( Control + Semicolon).
  • Then press Space.
  • Now press Ctrl+Shift+; (Control+Shift+semicolon).

another keyboard shortcut to how to enter date automatically in excel when data entered

  • Finally, you see that date as well as time as input in the cell.

APPROACH

2. Using Excel Functions

There are many functions in Excel to make mathematical calculations easier. In this article, we are going to discuss some functions of Excel to enter date automatically when one enters date. 

USING EXCEL FUNCTIONS

2.1 Using TODAY Function

To use the TODAY Function, you need to follow the below steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, go to the TODAY Function sheet.
  • Then select the D5 cell.
  • Now write the formula on the cell-

              =TODAY()

  • Now press Enter.

adopting TODAY Function to how to enter date automatically in excel when data entered

  • Finally, you will see the date on cell D5.

result of TODAY Function to how to enter date automatically in excel when data entered

Undoubtedly, we can say that using the TODAY Function is one of the easiest ways to enter date automatically in Excel when data is entered.

USING EXCEL FUNCTIONS

2.2 NOW Function

Another function in Excel is the  NOW Function to enter the date automatically when data is entered. We are going to discuss it for you below.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the NOW Function sheet.
  • Then select cell D5.
  • Then write the formula here:

            =NOW()

  • Now tap Enter.

NOW Function to how to enter date automatically in excel when data entered

  • Eventually, you will see the date and time in the cell.

result of NOW Function how to enter date automatically in excel when data entered

It is for sure that using the NOW Function is very simple to enter a date automatically while entering a date in Excel.

USING EXCEL FUNCTIONS

2.3 IF and NOW Functions Combined

There are many Functions in Excel. You can use them whenever you need. In this article, we’ve used IF and NOW Functions together to enter date automatically in Excel when data is entered. Follow the steps given below and you’ll be able to use those functions.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select a particular cell. We’ve chosen cell D5.
  • Now write down the following functions on the Formula Bar.

         =IF(C5<>””,IF(D5=””,NOW(),D5),””)

🔨 Formula Breakdown

It is a Timestamp function. It indicates that when C5 is empty, then D5 is empty too. And when we give input in C5, then D5 will get value simultaneously. We can do this by simply applying IF and NOW Functions together. IF is for the condition and NOW will give us time when one enters the date.

IF & NOW Functions to how to enter date automatically in excel when data entered

  • Then go to the File and select Options.

  • After selecting the Options, you will find a dialogue box.
  • Select the Formulas.
  • Click on the Enable Iterative Calculation.
  • Lastly, click on OK.

  • Now go back to the Excel sheet.
  • Write something on cell C5 and then tap Enter.
  • Finally, you will get the date as well as time on cell D5.

result of IF & NOW Functions to how to enter date automatically in excel when data entered

  • Similarly, if you select the function for cell D6 and write something on cell C6, you will get the date and time on cell D6.

result of IF & NOW Functions to how to enter date automatically in excel when data entered

Surely, using IF and NOW Functions together is one of the simplest ways to enter date automatically when data is entered in Excel.

APPROACH

3. Utilization of Autofill Option

If you intend to enter a date in Excel automatically when data is entered, then utilization of the Autofill option is one of the best approaches for you. There are many choices in the Autofill option.

UTILIZATION of AUTOFILL OPTION

3.1 Autofill Dates Serially by Increasing One Day

You can maintain the following steps to autofill dates serially by increasing one day.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select the cell that contains the date.
  • Then using the Fill Handle icon drag the column like below.

autofill option to how to enter date automatically in excel when data entered

  • Finally, you see that the days are increased by one day.

result of autofill option to how to enter date automatically in excel when data entered

UTILIZATION of AUTOFILL OPTION

3.2 Autofill Dates of Only Weekdays

To do this you need to follow the below-mentioned procedures:

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, Select the entire D column using the Fill handle icon.
  • Now Right-Click.
  • Secondly, you will see a menu and select the Fill Weekdays option from there.

autofill option to fill weekdays to how to enter date automatically when data entered in Excel

  • Consequently, you will find the weekdays in the particular column.

results of autofill option

UTILIZATION of AUTOFILL OPTION

3.3 Autofill Dates by Months or Years

This is another tactic for entering date automatically in Excel when data is entered. Just follow the below-mentioned steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, Select the entire D column using the Fill handle icon.
  • Now Right-Click.
  • Secondly, you will see a menu and select the Fill Months option from there.

autofill option buy months

  • Consequently, you will find the months in the particular column.

result of autofill option

If you want to autofill dates with years, you can do this by following the below steps-

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, Select the entire D column using the Fill handle icon.
  • Now Right-Click on the mouse.
  • Secondly, you will see a menu and select the Fill Years option from there.

autofill option by years

  • Eventually, you will get the years on that column consecutively.

autofill option result

UTILIZATION of AUTOFILL OPTION

3.4 Autofill Dates by 2nd or 3rd or Nth Days

You can also use the Autofill option to give the input of a series of dates. You just have to maintain the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, Select the entire D column using the Fill handle icon.
  • Now Right-Click on the mouse.
  • Secondly, you will see a menu and select the Fill Series option from there.

using of autofill by series

  • Consecutively, you will get the series of dates through the entire column.

autofill feature result

Undoubtedly, all the options of Autofill are very easy to maneuver and very simple as well as user-friendly.

APPROACH

4. Using Fill Series Feature

There are many features in Excel to do various types of calculations. The Fill Series option is one of those. If you want to enter date automatically in Excel when data is entered, you can use this option by maintaining the below-mentioned steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Fill Series sheet.
  • Then select the entire D column.
  • Now select the Home option.
  • Then select Editing.
  • You will see the Fill option there. Click on that option.
  • Now click on the Series option.

fill series option

  • Secondly, you will see a dialogue box named Series.
  • Select Columns in Series in, Date in Type, and Day in Date unit.
  • Give the Step value: 2.
  • Now click OK.

  • Eventually, you will see the days have come gaping by two days.

Fill series result

  • Again follow the same steps-

Home >> Editing >> Fill >> Series 

  • Then again you will get a similar dialogue box named Series.
  • Select Columns in Series in, Date in Type, and Weekday in Date unit.
  • Give the Step value: 2.
  • Now click OK.

  • Consequently, you will find the weekdays on that column gaping by two days.

fill series consequence

  • Further, follow the same steps-

Home >> Editing >> Fill >> Series 

  • Then again you will get a similar dialogue box named Series.
  • Select Columns in Series in, Date in Type, and Month in Date unit.
  • This time give the Step value: 3.
  • Now click OK.

Fill series by months

  • Consequently, you will find the months on that column gaping by three months.

Fill series result

  • Similarly, follow the same steps-

Home >> Editing >> Fill >> Series 

  • Then again you will get a similar dialogue box named Series.
  • Select Columns in Series in, Date in Type, and Year in Date unit.
  •  This time give the Step value: 3.
  • Now click OK.

  • Consequently, you will find the years on that column gaping by three years.

Assuredly, using the fill series feature in Excel is one of the best methods of entering date automatically while entering date.

APPROACH

5. Using VBA Macro

 When you intend to enter a date automatically in Excel when data is entered, then using the VBA code is one of the best choices for you. Executing VBA IS not very tough. You just have to maintain the following procedures.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, tap Alt+F11, and eventually, it will open the  VBA window on your Excel tab.
  • Then select Insert open.
  • Now click on the Module.

module on VBA window

  • When you find the Module, just write down the code given below.

VBA Code

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

VBA code

  • Now close the VBA window and return to the Excel sheet.
  • Then select a cell where you want to put the date (in our case we’ve selected cell D5).
  • Now write the following function on the formula bar.

=MFDate(C5)

  • Now press Enter.
  • Consequently, you will see the date as well as time on cell D5.

result of using VBA code

  • Similarly, if you write the following function on cell D6, you will get the same result for cell D6.

=MFDate(C6)

  • Similarly, you can find the date of the respective cell as well just by changing the cell name in the function.

Finally, you can clearly understand that using VBA code to enter date automatically in Excel when data is entered, is a very user-friendly method.


How to Auto Populate Date in Excel When Another Cell Is Updated

Auto-populating a date in Excel when another cell is updated is one of the major techniques for entering dates automatically. You can easily achieve this technique by maintaining the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, tap Alt+F11. Eventually, it will open a VBA window in your Excel sheet.
  • Now select Sheet9 (Auto-Populate date).

Module for auto populating date

  • It will open a Module tab.
  • Now select the Worksheet from the General menu.
  • Click on the Change from the SelectionChange.

  • Now delete the two lines below the horizontal line.

  • Write the code (given below) between the two lines.

VBA Code

With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("C5:C8"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With

  • Your code will look like the picture given below.

code for auto populating date

  • Now go to the Menu Bar.
  • Click on the Save option to save the code.

  • Then go back to the Excel sheet.
  • Write something on cell C5 and press Enter.

  • Consequently, you will get the date and time on cell D5.

result of auto populating date

  • Similarly, if you write something on cell C7, you will find values on cell D7.

result of auto populating date in Excel

  • It means whatever you write from cell C5 to C8, you will get values (date and time) on cell D5 to D8 consecutively.

Assuredly, this is a very simple process to auto-populate the date in Excel when another cell is updated.


📄 Important Notes

While entering the date automatically in Excel when data entering data, you need to be aware of the following things-

<img class="emoji" role="img" draggable="false" src="https://s.w.org/images/core/emoji/14.0.0/svg/1f58a.svg" alt="🖊️" />  You must press the proper keys when you intend to use Keyboard Shortcuts.

🖊️  While using the VBA code, you have to define the function properly.

<img class="emoji" role="img" draggable="false" src="https://s.w.org/images/core/emoji/14.0.0/svg/1f58a.svg" alt="🖊️" />  When you try for the auto-populating date in Excel, you need to specify the Cells clearly.


📝 Takeaways from This Article

If we summarize the whole article, we basically have tried to show you – how to enter date automatically in Excel when data is entered with some approaches.

📌  Firstly, in Approach 1, we’ve shown you the use of Keyboard Shortcuts.

📌  Secondly, in Approach 2, we applied various types of Excel Functions here:

1. TODAY Function.

2. NOW Function.

3. IF and NOW Functions

📌  Thirdly, in Approach 3, we’ve used the Autofill Feature of Excel to enter the date automatically when entering data.

📌  Fourthly, in Approach 4, we adopted the Fill Series Command.

📌  Fifthly, in Approach 5, we’ve tried to use VBA code which is a very important process to enter dates automatically in Excel when entering data.

📌  Finally, we have shown you the process of auto-populating the date in Excel when another cell updates.

Mainly we’ve tried to make you understand different strategies to enter dates automatically in Excel when entering data.


Conclusion

We wish that you would be able to enter the date automatically when data entered in Excel using this article. As several methods have been shown here, you can easily follow which one is comfortable for you. If there is any skepticism in your mind, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.

(Visited 78 times, 1 visits today)

Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo