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
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
Were the cells the formula refers to already filled? If so, you'll get the same time as the cell you dragged down from. The formula will only work correctly if the cells you refer to are blank when you create the formula.
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
Did you follow all the instructions from my first reply? You have to enable iterative calculation before entering the formulas.
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!
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.
