Calculate the elapsed with multiple break

[Dannyblr]

The user will change the cell value from Inprogress ->Pass or InProgress ->Pause->InProgress->stop.Now i want to calculate the time when the cell has InProgress value.


The user might select Pause several times but that Pause ->InProgress(break time )should not be taken.Please suggest how to implement this macro or circular reference.


Also it will be implemented in shared workbook

 

[Luke M]

Here's an idea to get your started. Right click on sheet tab, view code, paste this in

[pre]

Dim RunTime As Double
Dim StopTime As Double
Dim TimeSwitch As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyCell As Range

'Where is the value being changed?
Set MyCell = Range("A1")

If Intersect(Target, MyCell) Is Nothing Then Exit Sub

Select Case UCase(MyCell.Value)
Case "IN PROGRESS"
StopTime = Now
Case "PAUSE"
RunTime = RunTime + (Now - StopTime)
Case "STOP"
RunTime = RunTime + (Now - StopTime)
'Now what?
MsgBox "In process for " & Format(RunTime, "hh:mm:ss"), vbOKOnly
Case Else
End Select

End Sub

[/pre]
Didn't have time to test this, but hopefully it helps.

 

[Dannyblr]

Please look at the sheet:

https://rapidshare.com/files/278423650/Execution_Summary.xlsm

Here the user need to select the option in Col N, the start time in Col S and End time in Col V. the elapsed time s calculated using the UDF and the function is used in Col AC.


Now it working fine but the problem we use this sheet as shared workbook the time stamp gets updated when other users update the sheet. both the start time and end time becomes same and elapsed to zero


please help me.it works fine if not used as shared one.Suggest any workaround.