Forum Discussion
Date of Last Modified for a Cell
First, enable iterative calculation. You have to do this only once.
- Select File > Options.
- Select Formulas.
- Tick the check box 'Enable iterative calculation'.
- Click OK.
Enter the following formula in A3:
=IF(A2<>"",IF(A3<>"",A3,NOW()),"")
Apply the following custom number format to cell A3:
m/d/yy" at "h:mm AM/PM
This formula is working in my excel sheet. HOWEVER... I have some cells that are data validation "lists" and the date formula does not update when the user chooses different values from the "list".
I noticed that the formula DOES work when you delete the entry out and choose a new option from the list.
Is there anything I can do to get this formula to execute correctly when the cell switches from one list value to another list value?
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Here is a one drive link to an example of my problem: https://agcocorp-my.sharepoint.com/:f:/p/ben_thul/EiuK-sppvBlMi2tbpWm1lfsBY72MhvTbvKhOECo_QtZ_MA?e=XPRHi5
Thank you! This is the expected behavior: the formula will only set the time stamp if there is no time stamp yet and the order status is changed to a non-blank value. After that, the time stamp will remain the same until the order status is cleared.
If you want the time stamp to be updated each time the order status changes, you cannot use a formula. It can only be done using VBA code. There are several examples of such code in this discussion.
- Thank you for the speedy reply Hans! I will send an example work book shortly.