turn NOW() function static

[curiouscoder]

i am unable to show/upload my excel file as im working on a work device.

the H column consists of statuses in a drop down list with each status having its own respective column that records a time stamp whenever the status is selected.

my issue is due to the =NOW() function each timestamp is dynamic and keeps updating until the next status in the drop down list is selected. as you can see the timestamps assigned for the second 2 "Assigned" statuses are the same since neither of them haven't been set to the next status as of yet.

I have provided formulas for column J and L and would really appreciate if someone could show me how the formula in each column could be adjusted to show the static =NOW() timestamp as opposed to volatile.

thank you

 

[bobsan42]

put the date and time manually in the cell or use vba to save the current timestamp as value. you cant make the result of the formula static.
to insert the current date under the cursor you can use Ctrl + ;
to insert the current time (hh:mm) under the cursor you can use Ctrl + :

 

[Fluff]

To have a static timestamp using a formula you will need to enable Iterative Calculations, which is generally considered to be a bad idea. If you want to go down that root have a look here How to insert timestamps in excel sheet using formulas

 

[curiouscoder]

put the date and time manually in the cell or use vba to save the current timestamp as value. you cant make the result of the formula static.
to insert the current date under the cursor you can use Ctrl + ;
to insert the current time (hh:mm) under the cursor you can use Ctrl + :

i know of this shortcut but the boss wants the dates to be automatically populated

 

[curiouscoder]

To have a static timestamp using a formula you will need to enable Iterative Calculations, which is generally considered to be a bad idea. If you want to go down that root have a look here How to insert timestamps in excel sheet using formulas

I have tried something similar from a video i saw on youtube but it didnt work for me. any guidance as to how I can edit the formulas given above to make it work?

 

[Fluff]

Can you post the formula.

 

[curiouscoder]

Can you post the formula.

=IF(H3="","",IF(H3='DROPDOWN CATS'!$A$8,NOW(),K3)) this is for the first status column
=IF(H3="","",IF(H3='DROPDOWN CATS'!$A$9,NOW(),L3)) this is the next one

 

[Fluff]

Thanks for that, try

=IF(H3="","",IF(H3='DROPDOWN CATS'!$A$8,IF(K3="",NOW(),K3),K3))

 

[bobsan42]

Thanks @Fluff - turns out it is never too late to learn something new
@curiouscoder - despite the neat trick you need to keep some things in mind:
- the values are still not fixed on the sheet e.g. if you delete the status the values go away, Undo will not bring them back
- if the status is not a valid one all stamps get populated
- changing a status back to a previous one does not remove an existing stamp
and probably some more minor issues ...

 

[curiouscoder]

Thanks for that, try

=IF(H3="","",IF(H3='DROPDOWN CATS'!$A$8,IF(K3="",NOW(),K3),K3))

Thank you beautiful stranger you are a god send!