macro which will record time for completing a particular task in Excel 2007

[dev.sutradhar1987]

Hi,


I want a macro which will record time for completing a particular task in Excel 2007.


The Concept is :-


Suppose Column A has a DropDown with 3 options(In Progress, Hold, Completed) and time will be calculated and shown in Column B

Conditions:

1. If In Progress TIME Starts

2. If Hold TIME Pauses.

3. If Completed TIME Stops and Calculated the Total Time Taken from (In Progress - Completed) and Stores the Final Time In Column B (Time Format HH:MM:SS)


Please could you help me out in this.


Waiting for your Earliest Feedback.

 

[Luke M]

We're going to need to use some cell during the task to keep track of start time/time passed. Let's assume that we use col C for last start time, and col D for accumulated time.

How's this for a start?

[pre]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'First, did a cell of interest change?
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
'Don't do anything if multiple cells change
If Target.Count > 1 Then Exit Sub

Dim prevStart As Date
Dim totTime As Date
Dim xChoice As String
Dim xRow As Long

'Turn of events since we're going to be making changes to the sheet and
'don't want to keep triggering this macro
Application.EnableEvents = False
Application.ScreenUpdating = False

xChoice = UCase(Target.Value)
xRow = Target.Row
prevStart = Cells(xRow, "C").Value
totTime = Cells(xRow, "D").Value

Select Case xChoice
Case "IN PROGRESS"
'Task begins, or has resumed
Cells(xRow, "C").Value = Now
Case "HOLD"
'Task is paused, calculate elapsed time
If prevStart = 0 Then
Cells(xRow, "D").Value = totTime
Else
Cells(xRow, "D").Value = totTime + (Now - prevStart)
End If
Cells(xRow, "C").ClearContents
Case "COMPLETED"
'Task is all done, calculate any elapsed time
'plus any time already accumulated
If prevStart = 0 Then
'Completed after a hold
Cells(xRow, "B").Value = totTime
Else
Cells(xRow, "B").Value = totTime + (Now - prevStart)
End If
Cells(xRow, "B").NumberFormat = "[h]:mm:ss"
Case ""
'Cell is cleared, do nothing
Case Else
MsgBox "Unrecognized text"
End Select

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

[/pre]

 

[dev.sutradhar1987]

Hi Luke,


Thanks a Ton it is working great.I just need another help regarding a Date.

Date Format is - "DD.MM.YYYY HH.MM AM/PM..


What is need is in a cell when the time exceeds 20.30 PM then the date should change to next day and the time should be 11.30AM..

I just need a formulae. i am trying it but the error is the i can't get it to 11.30AM.


Please help me out with this..

 

[Luke M]

Is this what you wanted?

=IF(MOD(A2,1)>=20.5/24,INT(A2)+1+11.5/24,A2)

 

[dev.sutradhar1987]

Hi Luke,


I am using the above code for time calculation but what i understood is it is only calculating it on the basis of 24 Hours. I need something which will calculate also in days and months & Hours together.


Ex- If I start it today and complete is after 3 days without putting it on hold then i should show as 3days 04:32:00 something like this and the clock should also run when the sheet is closed.