MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Fixed Date after Selection


Posted by chuck mackillop on February 02, 2002 7:58 AM

I have a running formula in my spreadsheet to indicate the time that a certain selection was made:
=IF($H6="Actioned",NOW(),"")

It works fine except that the time presented keeps changing. I want to be able to fix the time that the selection was made.

Probably something really easy, but I am stymied!!


Posted by Yogi Anand on February 03, 2002 11:59 AM

Hi Chuck:
Since you are using the now() function to capture the date and time, you are recording the current date and time, dynamically.
If you can manually enter the date and time, you can use CTRL+; and CTRL+(Shift)+: to pick up the current date and time, and these will remain static ... will not change dynamically.
Now, I believe you prefer an automated solution --there are several options, including the following:
1) as soon as a selection is made, and the date and time is recorded, you can do a copy|paste special|values on the same cell
2) if you do not have any calculations being made in the mean time, before the worksheet is recalculated, you could have the column in which the date and time were recorded copy|paste special|valued
3) you can use a macro approach where this process can be automated
4) hopefully others may be able to suggest other options as well
HTH

Yogi Anand
ANAND Enterprises http://www.handtech.com/anand