Forum Discussion
Date of Last Modified for a Cell
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.
HansVogelaar --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?
I know I have it populating in C, this was for demo. 🙂
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.
- Thank you very much!
