Mursalin Ibne Salehin
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.
Download Practice Book
Download the practice book from here and exercise it to test your skill.
3 Easy Ways to Insert Excel Timestamp When Cell Changes Without VBA
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.
- Secondly, select Options. It will open the Excel Options window.
- Thirdly, select Formulas in the Excel Options window.
- Then, check Enable iterative calculation and click OK to proceed.
- After that, create a helper column in your dataset. We have named it the Previous Level.
- 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.
🔎 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.
- After applying the changes, you will see the timestamp correctly.
- 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.
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.
- Similarly, drag down the Fill Handle in Column E.
- 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.
- Moreover, you can hide the helper column to make the dataset look more understandable.
Read More: How to Automatically Insert Timestamp Data Entries in Excel (5 Methods)
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.
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.
- Secondly, select Cell E5 and type Ctrl + Shift + : on the keyboard to enter the time.
- 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.
- 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.
Similar Readings
- How to Insert Static Date in Excel (4 Simple Methods)
- Convert Unix Timestamp to Date in Excel (3 Methods)
- How to Insert Excel Date Stamp When Cells in Row Are Modified
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.
- In the second step, click on Options. It will open the Excel Options window.
- 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.
Read More: Excel VBA: Insert Timestamp When a Macro Is Run
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. Visit the ExcelDemy website for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
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
Hi GURIA,
Every time I saved the file timestamp changes.
What should I do?
Thank you.
Hello Mike,
Thanks for sharing your problem. It seems fine for me when I tried to save the excel file shared in this article. The timestamp does not change in my case. Therefore, I will suggest you go through the steps carefully, no matter which method you prefer. Also, please check if the Enable iterative calculation option in File > Options > Formulas section is turned on. If it still changes then try the VBA code shared in this article.
https://www.exceldemy.com/timestamp-in-excel-when-cell-changes/#2_Apply_VBA_Code_to_Insert_Timestamp_in_Excel_When_Cell_Changes
Let us know if you can solve the issue. Otherwise, please send us the excel file that you are working on at this address [email protected].
Regards,
Guria
ExcelDemy.