Forum Discussion
paulc1545
Dec 05, 2022Copper Contributor
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
Sort By
- bb5931Copper Contributor
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()), "")
- bb5931Copper Contributor
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
- 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?
- 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?
- 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.