How to Insert Excel Timestamp When Cell Changes Without VBA (3 Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn to insert an Excel timestamp when a cell changes without VBA. A timestamp is a record of the time and date of a particular event or task. In Microsoft Excel, we can easily insert a timestamp using VBA. But, Today, we will show how we can insert a timestamp when a cell changes without VBA. Here, we will demonstrate 3 easy ways. Using these methods, you can place a timestamp easily without the help of VBA.


How to Insert Excel Timestamp When Cell Changes Without VBA: 3 Easy Ways

To explain the methods, we will use a dataset that contains information about the experience level of some employees. Here, we will try to add the timestamp if someone changes the cell value of the Level column. For example, if someone types B in place of A in Cell C5, then a timestamp will be inserted in Cell D5. So, without any delay, let’s observe the methods.


1. Apply Formula to Insert Excel Timestamp When Cell Changes

In the first method, we will apply a formula to insert a timestamp when the cell changes. To create the formula, we will use the combination of the IF, AND & NOW functions together. The IF function examines if a condition is met or not. The AND function returns TRUE if all conditions are met. And the NOW function gets the current date and time. Let’s follow the steps below to learn the method thoroughly.

STEPS:

  • In the first place, click on the File tab.

Apply Formula to Insert Excel Timestamp When Cell Changes

  • Secondly, select Options. It will open the Excel Options window.

Apply Formula to Insert Excel Timestamp When Cell Changes

  • Thirdly, select Formulas in the Excel Options window.
  • Then, check Enable iterative calculation and click OK to proceed.

Apply Formula to Insert Excel Timestamp When Cell Changes

  • After that, create a helper column in your dataset. We have named it the Previous Level.

Apply Formula to Insert Excel Timestamp When Cell Changes

  • Now, type the formula in Cell D5:
=IF(AND(C5<>"",E5<>C5),NOW(),IF(C5="","N/A",D5))
  • And press Enter to see the result.

Apply Formula to Insert Excel Timestamp When Cell Changes

🔎 How Does the Formula Work?

Here, we have 3 arguments inside the IF function.

  • AND(C5<>””,E5<>C5)

The first argument denotes logic. It states that Cell C5 will have to store a value and the value will have to be the same as Cell E5.

  • NOW()

The second argument is the value that will be stored in Cell D5 if the condition is met. In this case, we have used the NOW function. That means it will store the date and time.

  • IF(C5=””,”N/A”,D5)

And the third argument says if Cell C5 is empty then, it will store ‘N/A’ in Cell D5.

  • In the following step, press Ctrl + 1 to open the Format Cells window.
  • At this moment, select Number >> Custom, and then, select m/d/yyyy h:mm.
  • Click OK to proceed.

Apply Formula to Insert Excel Timestamp When Cell Changes

  • After applying the changes, you will see the timestamp correctly.

Apply Formula to Insert Excel Timestamp When Cell Changes

  • Again, type another formula in Cell E5:
=IF(C5="","",IF(OR(D5="",AND(ISNUMBER(E5),C5=E5)),E5,C5))
  • Press Enter to see the result.

Apply Formula to Insert Excel Timestamp When Cell Changes

Here, we have combined the IF, OR, AND along with the ISNUMBER function. This formula stores the value of Cell C5 so that if we make any changes, it doesn’t apply those changes in other cells. For example, if you don’t apply the formula then, the timestamp will be updated if you change anywhere in the sheet. To prevent that, we have applied this formula. It will store the value of Cell C5.

  • Now, use the Fill Handle in Column D.

Apply Formula to Insert Excel Timestamp When Cell Changes

  • Similarly, drag down the Fill Handle in Column E.

Apply Formula to Insert Excel Timestamp When Cell Changes

  • Finally, if you make any changes to the Level column, then, the timestamp will be updated.
  • In our case, we changed the value of Cell C6 and the timestamp was updated.

Apply Formula to Insert Excel Timestamp When Cell Changes

  • Moreover, you can hide the helper column to make the dataset look more understandable.

Apply Formula to Insert Excel Timestamp When Cell Changes

Read More: How to Insert Last Modified Date and Time in Excel Cell


2. Insert Timestamp Without VBA Using Keyboard Shortcuts in Excel

We can also use some keyboard shortcuts to insert a timestamp in Excel without VBA. Unfortunately, the dataset will not update automatically in this method. You need to do that manually. But the important thing is that this process is very quick and easy to apply. To explain the method, we will use two separate columns named Date and Time instead of Timestamp. You will find the answer to the reason behind doing this in the steps of this method.

Insert Timestamp Without VBA Using Keyboard Shortcuts in Excel

So, without further ado, let’s pay attention to the steps below.

STEPS:

  • First of all, select Cell D5 and press Ctrl + : on the keyboard. It will insert the date in that particular cell.

Insert Timestamp Without VBA Using Keyboard Shortcuts in Excel

  • Secondly, select Cell E5 and type Ctrl + Shift + : on the keyboard to enter the time.

Insert Timestamp Without VBA Using Keyboard Shortcuts in Excel

  • As we don’t have any shortcuts to enter the time and date together, we need to insert them in separate cells.
  • Now, do the same for the rest of the dataset.

Insert Timestamp Without VBA Using Keyboard Shortcuts in Excel

  • In the end, if you change a value, then you need to use the keyboard shortcuts and update the timestamp like in the picture below.

Insert Timestamp Without VBA Using Keyboard Shortcuts in Excel


3. Use Excel Circular References to Insert Timestamp When Cell Changes

In the last step, we will use circular references to insert timestamps when the cell changes. Circular reference means using the same cell in the formula where you are applying it. Suppose, you want to apply ‘=A4+A5+A6’ in Cell A6. Then, here you are applying circular reference. Because the formula is using the value of Cell A6. Let’s follow the steps below to learn the process of applying the formula in this case.

STEPS:

  • To begin with, click on the File tab.

Use Excel Circular References to Insert Timestamp When Cell Changes

  • In the second step, click on Options. It will open the Excel Options window.

Use Excel Circular References to Insert Timestamp When Cell Changes

  • Thirdly, select Formulas in the Excel Options window and check Enable iterative calculation.
  • Then, click OK to proceed.

  • The important thing is that you need to keep the Level column empty this time.

  • Now, select Cell D5 and type the formula:
=IF(C5<>"",IF(D5<>"",D5,NOW()),"")

Here, the formula states that if Cell C5 is empty and we put a value in Cell C5, then, Cell D5 will store the timestamp. Unfortunately, it will not update automatically, if you change the value after typing a value. If you want to update it, then you need to clear the contents of Cell C5 first and then type the new value.

  • After typing the formula, press Enter and drag down the Fill Handle.
  • You will not see anything now.

  • In the following step, type A in Cell C5 and you will see the timestamp in decimal form.

  • In order to correct it, select the cells where you have applied the formula.

  • In the next step, press Ctrl + 1 to open the Format Cells window.
  • In the Format Cells window, select Number >> Custom and type m/d/yyyy h:mm:ss in the ‘Type’ field.
  • Then, click OK.

  • Lastly, if you type value in the blank cells of the Level column, the timestamps will be updated automatically.


Download Practice Book

Download the practice book from here and exercise it to test your skill.


Conclusion

In this article, we have demonstrated 3 easy ways to insert Excel Timestamp When Cell Changes Without VBA. I hope this article will help you to perform your tasks easily. Moreover, if you want an automatic update after each change, then follow Method-1. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles


<< Go Back to Timestamp | 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.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

4 Comments
  1. This is terrific. I’m doing exactly what I wanted to do without VBA. However using your method 1, when I establish a custom date format for the timestamp such as “mm-dd-yyyy hh:mm:ss AM/PM”, it displays as expected, but when I return and change the value in cell C5 (what you refer to as “Level”, the timestamp changes as it should except that the format has reverted to a similar but different custom format “mm/dd/yyyy, hh:mm:ss AM/PM”. This is not a show-stopper and I don’t believe it is related to your coding, but I am wondering why it will not retain my original custom format. Thanks for your infor related to changing a single timestamp without all the others changing.

    • Hello Tony Woods,
      Thank you for sharing your query. I have tried to change values in the Level column and the timestamp changes according to the custom date format you shared. So it seems there is nothing wrong with your preferred custom format or the method that we described in the article. Therefore, I have come to the following solutions to your problem.
      Solution 1: Change the Text format in the Timestamp column
      For this, go to Data > Text to Column.
      Then, in the second step, keep all the Delimiters unchecked.

      Lastly, select Date as the Column Data Format > choose your preferred format from the drop-down list > press Finish.

      Solution 2: Check settings in Formulas section
      For this, go to File > Options and then check if this setting is active in your workbook.

      Solution 3: Check settings in Proofing section
      For this, go to go to File > Options and then check if the settings in the Proofing section are active in your workbook.

      I hope the solutions will help you to work efficiently. Let us know your feedback.
      Regards,
      Guria,
      Exceldemy

  2. Hi GURIA,

    Every time I saved the file timestamp changes.
    What should I do?

    Thank you.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo