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?

  • 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

    • 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.

    • 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?

    • 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?

Share