Date of Last Modified for a Cell

Copper Contributor

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?

69 Replies

@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

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.

@paulc1545 

That would be

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

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

@Hans Vogelaar 

Hi Hans!
Thank you for this very useful and simple solution.

Unfortunately, the format is not working very well.

The "year" can only show "yy", not the year numbers.

And it's showing a way off date here (today 01/17/2023) but it reads "21/12/yy at 12:00 am" - now 11:21 am

Do you have any idea why it can figure out the right date or time?

 

Thank you very much in advance!

@marceloanexa 

The format code yy works for the English language version of Excel.

If your version uses another language, you probably have to use another letter.

For example, for French, Spanish, Italian and Portuguese it would be aa (for année, año, anno, ano), and in German and Dutch it would be jj (for Jahr, jaar), etc.

Thank you very much!
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?

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

@Codes1141 

The formula will work if column C is initially empty.

If you then enter something in a cell in column C, the current date and time will be entered in the corresponding cell in column E, and it won't change anymore.

@Hans Vogelaar 
Amazing formula, I've got a use case for it too in one of my query table but whenever the query refreshes, the date jumps back to 1/0/00 at 12:00AM

Any idea why?

 

@WeeHooo1915 

I guess this won't work with a query table. You'd probably need VBA code instead of a formula.

@Hans Vogelaar 

Thanks for this formula. When I am trying it on the my excel sheet, it displays date as 01/00/1900, 12:00AM, and not the current date and time. Do you know what error I might be making? Thanks

@ClearRock 

Did you follow all the instructions from my first reply? You have to enable iterative calculation before entering the formulas.

@Hans Vogelaar --thanks for helping all of us!

I read all of the previous replies. I cannot see the Drive file so I am hoping you can help me with my error. 

I want column B to autofill with the date when its corresponding cell in column A is edited. Am I going about this incorrectly?

bridgettedawn_0-1692370934008.png

I know I have it populating in C, this was for demo. :)

@bridgettedawn 

If you want to enter the date in column B when the corresponding cell in column A is filled for the first time, you can use a formula. (Or did you mean column C instead of column A?)

 

If you want to enter the date in column B whenever the corresponding cell in column A is edited, you'll need VBA code.

 

Which do you want?

@Hans Vogelaar 

I want the date in B to autofill/update whenever changes are made to the corresponding cell in column A. I thought I wanted the same thing the original post stated. Minus the time (not necessary) and the fact that they wanted their info on the same column? What could I change to be able to use the formula you originally gave?

@bridgettedawn 

If you want the date in column B to be updated whenever you change the corresponding cell in column A, you cannot use a formula. You'll need VBA code.

Do the following:

  • Right-click the sheet tab.
  • Select 'View Code' from the context menu.
  • Copy the code listed below into the worksheet module.
  • Switch back to Excel.
  • Save the workbook as a macro-enabled workbook (*.xlsm).
  • Make sure that you allow macros when you open the workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A2:A" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Intersect(Range("A2:A" & Rows.Count), Target).Offset(0, 1).Value = Date
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
I have actually never used VBA code-ever. So I am excited to learn. What do I do once I get back over to Excel? I saved as macro enabled and closed the file.