Forum Discussion
Current Date and time per cell
I have a table with one column listing rows with a dynamic text box (open; closed) in the next column I would like to display the date and time when the status changes.
The purpose is to show when a task was completed, I have used the now() function but it keeps refreshing, I need it stay the date
The snip of the sheet below shows how is is currently working, so can any one help with some code for column J please.
thank you
Lee
3 Replies
You need VBA coding to stay date. NOW() function will change the date/time once the cell is recalculated.
You require a logic test that applies the Now() stamp when the criteria is met - a fresh change to “Closed” in the monitored cell - but when not met leaves the check cell as it was.
Solution:=if(AND(i9=“Closed”,Cell(“address”,i9)=Cell(“address”)),Now(),j9)
Based on:Date of Last Modified for a Cell | Microsoft Community Hub
This requires iterative calculation setting to be turned on, details of which can be found in the thread linked above.
I have assumed that the cell update test { Cell(“address”,i9)=Cell(“address”) } will work for a dynamically updated rather than user updated cell - this needs to be tested.
I have tested and unfortunately this does not work for dynamically updated cells as it doesn’t register with the Cell(“address”) function. It does work for manually updated cells though.
