Forum Discussion
Date of Last Modified for a Cell
Good day, all. I am inquiring if there is an excel function that would give me a date (and time) that an individual cell was last modified.
For example:
cell A2 has an entry of $2.55 and I'd like cell A3 to read "12/5/22 at 10am".
a week later I change the $2.55 to $3.00....I'd like the A3 cell to read "12/12/22 at 10:30am".
I've seen some VBA solutions but wondering if there is a simple excel formula to accomplish this?
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
- paulc1545Copper ContributorHans - Thank you for the response. I made an error in my question, I'd like the entry to be in A2 and the date modified in B2. How would that change your formula? Thanks for the help.
That would be
=IF(A2<>"",IF(B2<>"",B2,NOW()),"")
Set the number format of B2 to the custom format from my previous reply.
- Codes1141Copper ContributorI'm trying to do the same thing as original post.
When I input this formula only two results come up: 1/0/00 at 12:00 AM or 2/3/23 at 6:34 PM (Today's Date/Time)
This file was created before today and the cells referenced in the cell with Today's Date weren't all modified today.
When I change a cell referenced in the cell with 1/0/00 at 12:00 AM the formula does not update to today's date.
What am I doing wrong?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?
- thulben5Copper Contributor
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?