NOW() Function help

[Rob69]

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.

[mangesh_yadav]

This is achievable through VBA only. Right click on the sheet-name tab and click view code, and enter the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$7" Then
Range("B7") = Now()
ElseIf Target.Address = "$Q$7" Then
Range("R7") = Now()
End If

End Sub


When the user enters a value in cell A7, cell B7 will be populated with the current time. And when the user checks the checkbox, the cell R7 will be populated with the current time. At this time B7 will not change its time value.

- Mangesh

[Jerry W. Lewis]

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.
>
>

[Alex Delamain]

Take a look at this site which will explain how you can put a date/ time stamp on.

http://www.mcgimpsey.com/excel/timestamp.html