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?
78 Replies
Hi All,
Have the same question as the OP but for modification of a cell within a group, i.e. the last modified timestamp appears in say cell M2 if any of J2, K2 or L2 have been modified. Tried something with IFS function but can't get it to work.
Currently using the below formula for timestamp of any modification inc. non-blank to non-blank: =IF(L2<>"",IF(AND(M2<>"",CELL("col")<=6,CELL("col")>=1,CELL("row")=ROW(L2)),NOW(),IF(CELL("address")<>ADDRESS(ROW(L2),COLUMN(L2)),M2,NOW())),"")
Thanks for your help!
Like this. Don't forget to enable Iterative Calculation, otherwise it won't work.
=IF(OR(J2:L2<>""), IF(M2<>"", M2, NOW()), "")
Thanks for your reply HansVogelaar.
This seems to only work if the modification is blank to non-blank? (Iterative Calculation is enabled)
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?
- I'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?
- Hans - 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.
