Forum Discussion
Date of Last Modified for a Cell
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?
Here is a one drive link to an example of my problem: https://agcocorp-my.sharepoint.com/:f:/p/ben_thul/EiuK-sppvBlMi2tbpWm1lfsBY72MhvTbvKhOECo_QtZ_MA?e=XPRHi5
- 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?
Here's a Google Drive link: https://drive.google.com/file/d/1RP6xgA8oyYEU1vnrzclOKKahs8MfGyJ5/view?usp=sharing
- 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.
Hi, I'm using the same formula, and it's working. However, when I drag this formula down the entire column, it references different cells instead of just one, causing the date and time to change for all cells to the same value
