Forum Discussion

paulc1545's avatar
paulc1545
Copper Contributor
Dec 05, 2022

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

  • bb5931's avatar
    bb5931
    Copper 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()), "")

      • bb5931's avatar
        bb5931
        Copper Contributor

        Thanks for your reply HansVogelaar.

         

        This seems to only work if the modification is blank to non-blank? (Iterative Calculation is enabled)

  • paulc1545 

    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

    • thulben5's avatar
      thulben5
      Copper Contributor

      HansVogelaar 

       

      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?

      • thulben5 

        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?

    • Codes1141's avatar
      Codes1141
      Copper Contributor
      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?
      • HansVogelaar's avatar
        HansVogelaar
        MVP

        Codes1141 

        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?

    • paulc1545's avatar
      paulc1545
      Copper Contributor
      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.
      • HansVogelaar's avatar
        HansVogelaar
        MVP

        paulc1545 

        That would be

        =IF(A2<>"",IF(B2<>"",B2,NOW()),"")

        Set the number format of B2 to the custom format from my previous reply.

Resources