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.

### 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()`

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

Additionally, you can practice it in the **C6 **cell.

### 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()`

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

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

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

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

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

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

- Secondly, choose
**Insert**> select**Module**.

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

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

Secondly, select **Fill Days**

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

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.

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

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

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.

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

Consequently, you’ll get the output like this.

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

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

in Cell K3, and copy it down column K, whenever you write something in column J, the`=IF(J3<>"",NOW(),"")`

NOW 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

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.)`=IF(J3<>"",IF(K3="",NOW(),K3),"")`

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,part ensures that the`IF(K3="",NOW(),K3)`

IF functionwill 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

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

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.