NOW() Function help
 

Hi all,

if anyone could help me with this problem I would be most grateful.

I need a formula that time and date stamps a cell when data is entered in to
another cell. I am trying to create a audit trail and monitor the time taken
to complete two tasks.

Basically person A is handed a task and inputs the task number on the sheet,
as soon as they input the task number this formula time stamps a cell.

=IF(ISBLANK(A7)=FALSE,NOW(),"")

Once person A has completed the task there is a CHECKBOX they can tick to
say they have completed the task. The checkbox has a linked cell which can
either be TRUE(TICKED) or FALSE(UNTICKED). I have another formula that time
stamps a cell if the linked cell is TRUE.

=IF(Q7=TRUE,NOW(),"")

The problem is, if you input the task number at say 10:00 it will time stamp
the cell 10:00 which is fine and is exactly what i want. Say the task takes
10 minutes and you return to the sheet to tick the checkbox it will then time
stamp the next cell with 10:10 but it will also update any other cells with
the NOW() function to 10:10 on the sheet which defeats the object of the
exercise.

Can anyone help me with this?

Thanks.

NOW() is a volatile function (it will update whenever anything is
entered or recalcualted). You will either have to manually Copy and
Paste Special|Values or else write a VBA event handler to put the value
instead of the function into the cell.

Jerry

Rob69 wrote:

Hi all,

if anyone could help me with this problem I would be most grateful.

I need a formula that time and date stamps a cell when data is entered in to
another cell. I am trying to create a audit trail and monitor the time taken
to complete two tasks.

Basically person A is handed a task and inputs the task number on the sheet,
as soon as they input the task number this formula time stamps a cell.

=IF(ISBLANK(A7)=FALSE,NOW(),"")

Once person A has completed the task there is a CHECKBOX they can tick to
say they have completed the task. The checkbox has a linked cell which can
either be TRUE(TICKED) or FALSE(UNTICKED). I have another formula that time
stamps a cell if the linked cell is TRUE.

=IF(Q7=TRUE,NOW(),"")

The problem is, if you input the task number at say 10:00 it will time stamp
the cell 10:00 which is fine and is exactly what i want. Say the task takes
10 minutes and you return to the sheet to tick the checkbox it will then time
stamp the next cell with 10:10 but it will also update any other cells with
the NOW() function to 10:10 on the sheet which defeats the object of the
exercise.

Can anyone help me with this?

Thanks.