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.
- 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:
- And press Enter to see the result.
🔎 How Does the Formula Work?
Here, we have 3 arguments inside the IF function.
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.
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.
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:
- 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.
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.
- 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.
- 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.
- 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:
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.
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.