Forum Discussion
Automatically adding the date/time to a cell when another cell is updated
NikolinoDE This works, but unfortunately it then changes the date and time for all the cells whenever the date and time changes. So it doesn't exactly work as an accurate way to show that a cell was filled on THIS DATE and THIS TIME, because, it continues to update.
See, in the image below, I entered text in C3 at 9:13, but as I added text to successive rows at 9:15, all the times changed to 9:15.
I am glad to have had this practice, though, so thank you for the tip. I am wondering if you or anyone else has a way to do what I am looking for in this "easy" way.
https://www.youtube.com/watch?v=YEyggg2z04c
Steps:
File> Options > Formulas [tab] > check "Enable iterative calculation" then change Maximum Iterations to "1"
Then use this formula for the cell where you want the time or date stamp:
=IF(C5<>"", IF(B5="", NOW(), B5), "")
* C5 would be the cell in which the date/time will appear, B5 represents the referenced cell. So in this example, when data is entered into B5, the time/date of entry will appear in C5.
After the step of "Enable iterative calculation" in options, this formula didn't work for me since it seems backwards from what was being asked.
This formula:
=IF(A2<>"",IF(B2="",NOW(),B2),"")
puts the time into B2 when data is entered into A2, if putting the time next to the data.To put the date below the data would require:
=IF(D6<>"", IF(D7="", NOW(), D7), "")I also had to save the file as a macro-enabled excel file (.xlsm) for it to not change the date for all the cells that I copied the first formula into. Before I did that, if I copied the formula in B2 into cells B3:B200, if I put data into any of the cells A2:A200, it would change all the times in cells B2:B200 to the same current time. But once I saved the file as a .xlsm, the time entered in B2 based on data in A2 did not change when I entered data into A3 or A4, etc.
