My Quest with Time!!

[Guest]

Hello Friends

Now this is a very simple concern that I put forth but which seemingly takes
a long time to answer!!

Well, lets look at the table below :

Order Status st Time End Time Time lapsed
112345 A 11:30 11:50 0:20
12456 N 11:55 12:40 0:45

Now, I want the third field(st Time) to show the current time when a user
enters the first field(Order) and field four(End Time) to show the current
time when a user enters the second field(Status) but only for Status 'A'.
Simple!!

Well, if so please help. I used the now() function but all cells keep
changing when a new cell is entered.

Thank you

Philip Jacob
Senior Executive Quality Appraisal
First American Corporation

 

[Mangesh Yadav]

You need an event code.

Right click on the sheet name tab and go to view code, and enter the
following code:


Private Sub Worksheet_Change(ByVal Target As Range)

if Target.Row = 1 then Exit Sub

If Target.Column = 1 Then
Cells(Target.Row, "D") = Now()
End If

If Target.Column = 2 And Target.Value = "A" Then
Cells(Target.Row, "E") = Now()
End If

End Sub


Assumptions:
Your data starts from cell A1.


Mangesh

 

[Paul Sheppard]

Hello Friends

Now this is a very simple concern that I put forth but which seemingl
takes
a long time to answer!!

Well, lets look at the table below :

Order Status st Time End Time Time lapsed
112345 A 11:30 11:50 0:20
12456 N 11:55 12:40 0:45

Now, I want the third field(st Time) to show the current time when
user
enters the first field(Order) and field four(End Time) to show th
current
time when a user enters the second field(Status) but only for Statu
'A'.
Simple!!

Well, if so please help. I used the now() function but all cells keep
changing when a new cell is entered.

Thank you

Philip Jacob
Senior Executive Quality Appraisal
First American Corporation

Hi Philip

Assuming data is in the range A1 to E3 in your example, try this
adjust the ranges as needed

In the third column enter this formula > =IF(A2="","",NOW())
In the fourth column enter this formula > =IF(B2="","",NOW())
In the fifth column enter this formula
=IF(A2="","",IF(D2="","",SUM(D2-C2))

 

[Guest]

Mangesh,

Excellent. Thank you very much. Exactly what I was looking for. Works very
well.

I s there a way I can include the code only once for many sheets. And the
target becomes the active sheet.

Thnaks again

Philip Jacob
Senior Executive Quality Appraisal
First American Corporation.

 

[Mangesh Yadav]

Enter the following code in the standard module

Sub MyMacro(Target As Object)

If Target.Row = 1 Then Exit Sub

If Target.Column = 1 Then
Cells(Target.Row, "D") = Now()
End If

If Target.Column = 2 And Target.Value = "A" Then
Cells(Target.Row, "E") = Now()
End If

End Sub



And for each sheet module enter:
Private Sub Worksheet_Change(ByVal Target As Range)

Call MyMacro(Target)

End Sub



Mangesh