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.

**2. ****Using the TODAY Function**

Excel has a default **TODAY 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.

**3. ****Using the NOW Function**

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

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

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, **Cell B2** will be empty too. And when 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 is entered.

**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 it down to the last cell you need for data entry in **Column B** & release the mouse button.

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

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

**Step 4:**

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

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

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.

**Similar Readings**

**How to Display Day of Week from Date in Excel (8 Ways)****Insert Last Saved Date in Excel (4 Examples)****How to Insert Drop Down Calendar in Excel (With Quick Steps)****Insert Date in Footer in Excel (3 Ways)****How to Insert a Date Picker in Excel (With Step-by-Step Procedure)**

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

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

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

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, go to 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.

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

Now, 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).

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.

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

**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 the **Module** command & now you’re ready to make your own formula by 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.

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

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

## Related Articles

**How to Insert Date in Excel (7 Simple Methods)****Combine Date and Time in One Cell in Excel (4 Methods)****How to Change Dates Automatically Using Formula in Excel****Insert Dates in Excel Automatically (3 Simple Tricks)****How to auto-populate date in Excel when a cell is updated****Excel Macro: Insert Date and Time in a Cell (4 Examples)**

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.

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

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

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

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!

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, theNOW functionwill 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 functionwill not return the current time when there is already a timestamp recorded. Look closely,IF(K3=””,NOW(),K3)part ensures that theIF functionwill return what is already in column K cells if there is any priorly.Regards,

Tanjim Reza