PDA

View Full Version : [SOLVED] Calculate time spent in each job role.



jazznaura
08-04-2015, 01:49 PM
Good Evening Everyone,

i'm in need of some expert advice.

Attach is sheet with table of jobs completed (job code, start datetime, end datetime and employee name).
I've used formulas and vba to calculate time spent on each job.

It works but its messy :dunno could one of you kind gentlemen/ladies have a look and advise how i could improve it or write it all in vba.
i've color coded data to show start and finish points.

There will be approx 50000 records daily with multiple job codes (20+) and multiple employees (50+).

thanks in advance;

J.Naura

Paul_Hossler
08-04-2015, 04:59 PM
Could you use pivot tables instead?

jazznaura
08-04-2015, 11:46 PM
Thanks for the reply Paul,

My attached file had below table, wish show what the results should be. I maybe missing something but i don't see any of the figures in your example coming close,



256
06:16:18


301
07:02:10



J.Naura

Bob Phillips
08-05-2015, 01:09 AM
I agree with Paul's number's, not yours.

jazznaura
08-05-2015, 04:48 AM
Sorry Paul you were correct, it had been a long day yesterday when i posted this, should have double checked my numbers :banghead::banghead:

One more question if i may, i noticed there are gaps in the data, where people are off the system for a numbers of hours, any way i can exclude those hours? (anywhere there's a gap of an hour or more between jobs)

Dick below on job code 256 worked 07:31 to 07:38 , then wasn't back on the system till 13:40 to 13:41 so worked total of 8 mins, instead of 6ish hrs.


thanks again,

J.Naura

Paul_Hossler
08-05-2015, 07:14 AM
Sorry, I don't see that in your data. Maybe I'm interpreting it wrong

14075


I see a total of 85 seconds (or 1 min, 25 sec = 1:25) not 8 minutes.

I'm not seeing the 6ish hours anywhere

Where are you seeing those?

jazznaura
08-05-2015, 07:32 AM
The data has flipped on that pic, on the workbook you posted back, dick worked on job 256 (CELL B11) from 07:31 TO 07:38 (C12) then he's off the system until 13:40 (b13) & works till 13:41 (c14).

wonder if we can exclude the time he's of the system.

thanks,

Paul_Hossler
08-05-2015, 10:44 AM
I don't see how Dick is off the system

The Start and End times determine how many seconds each person works each job and the pivot tables only add the numbers

The Data sheet is sorted by Job and then Employee, but the pivot tables are used for analysis and presentation

I did change the formatting to show seconds on Start and End, but it still looks correct to me

jazznaura
08-05-2015, 11:51 AM
I'm doing a really bad job of explaining this,:crying:

in your last screenshot ...

dick starts job type 256 at 07:31:18 (b2) & continues that type off job until 07:38:02 (next line c3)

then he doesn't appear on the system again until 13:40:55 (next line b4) & continues that type of job until 13:41:48 (c5)

what i'm trying to do is calculate how much time spent doing a type of job.

from 07:31:18 to 07:38:02
then back again from 13:40:55 to 13:41:48

its the time spent on types of jobs , not time worked by employee.

problem is the start and end times (eg B2 to B3) are not accurate. so i have to look at the span of time on each job type and use the time gaps (eg C3 & B4) as my markers for start & end times.

i'm not sure if that's a better explanation, but thanks for having a look at this.

J.Naura

Paul_Hossler
08-05-2015, 02:21 PM
1. I think I understand what you're wanting to do. The 'correct' answer for Dick in job 256 is 7 min, 37 sec, not the 1 min, 25 sec (J - Q vs. A - H in screenshot)?

2. Your data cannot be trusted, so you want to 'consolidate' the start time with the 'correct' end time (B2 with C3 for example)

3. What are the rules to pair the correct end time with each start time? Within 5 minutes, 1 hour, 6 hours, 1 day, etc.?

4. It is possible that (for ex) B2 might have an end time in C7 if Dick started and stopped 6 times in your data

5. BTW, there is an approx. 6 min 'gap' between rows 2 and 3 and an approx. 2 min gap between rows 3 and 4.

6. It would be a LOT easier if you could change the way you collect data


14078

jazznaura
08-06-2015, 03:33 AM
1 & 2 :biggrin: yep, you've explained it much better than me, thanks for sticking with this, really appreciated.

3 - I been working with, if there's a gap of greater than 15 mins, then its not continuous, so that's the end time.

4 - unfortunately yes, i have data were an employee did the same job type continuously for most of the shift, so StartTime EndTime spans 300 rows.

5 - yep as long as its not greater than 15 mins, i count it as continuous work.

6 - tell me about it , this is from a brand new automated system that's been put in multiple Warehouses, that records data to an Oracle db, what you're seeing has been cleaned up.

Paul_Hossler
08-06-2015, 12:36 PM
I still like to use pivot tables for presentation and analysis for stuff like this, but sometimes a macro is required to clean the data




Option Explicit
Sub FixDataAndRefreshPivotTables()
Const colJob As Long = 1
Const colStart As Long = 2
Const colEnd As Long = 3
Const colEmp As Long = 4
Const colElapsed As Long = 5

Dim tCloseEnough As Double, tNumberSeconds As Double
Dim wsRaw As Worksheet, wsClean As Worksheet
Dim rClean As Range, rCleanLessHeaders As Range
Dim iRow As Long

Dim pt As PivotTable
Dim ws As Worksheet



'ask user
If MsgBox("Do you want to clean the raw data?", vbQuestion + vbYesNo, "FixDataAndRefreshPivotTables") = vbNo Then Exit Sub

Application.ScreenUpdating = False

'one day / 24 hours / quarter hour
tCloseEnough = 1# / 24# / 4#


Set wsRaw = ActiveWorkbook.Worksheets("Data")

'delete WS Clean if it exists
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets("Clean").Delete
Application.DisplayAlerts = True
On Error GoTo 0

'copy WS Data as Clean so we leave original data alone
wsRaw.Copy after:=wsRaw
Set wsClean = ActiveSheet
wsClean.Name = "Clean"

'sort WS Clean by Job - Emp - Start
If wsClean.AutoFilterMode Then wsClean.AutoFilterMode = False

Set rClean = wsClean.Cells(1, 1).CurrentRegion
Set rCleanLessHeaders = rClean.Cells(2, 1).Resize(rClean.Rows.Count - 1, rClean.Columns.Count)

With wsClean.Sort
.SortFields.Clear
.SortFields.Add (rCleanLessHeaders.Columns(colJob))
.SortFields.Add (rCleanLessHeaders.Columns(colEmp))
.SortFields.Add (rCleanLessHeaders.Columns(colStart))
.SetRange rClean
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With



'loop up WS clean
'if job(n)=job(n-1) AND emp(n)=emp(n-1) and start(n) < 15 min after end(n-1)
'make end(n-1) = end(n) and then delete row n
With rClean
For iRow = .Rows.Count To 3 Step -1
If .Cells(iRow, colJob).Value = .Cells(iRow - 1, colJob).Value And _
.Cells(iRow, colEmp).Value = .Cells(iRow - 1, colEmp).Value And _
Abs(.Cells(iRow, colStart).Value - .Cells(iRow - 1, colEnd).Value) <= tCloseEnough Then
.Cells(iRow - 1, colEnd).Value = .Cells(iRow, colEnd).Value
.Rows(iRow).EntireRow.Delete
End If
Next iRow
End With
'might as well compute times while we're here
With rClean
.Cells(1, .Columns.Count).Offset(0, 1).Value = "Time Elapsed"

For iRow = 2 To .Rows.Count
tNumberSeconds = (.Cells(iRow, colEnd) - .Cells(iRow, colStart)) * 24 * 60 * 60
.Cells(iRow, colEmp).Offset(0, 1).Value = TimeSerial(0, 0, tNumberSeconds)
Next iRow

.Columns(colElapsed).NumberFormat = "h:mm:ss;h:mm:ss;;@"

End With
rClean.Cells(1, 1).CurrentRegion.Name = "Data"


'refresh pivot tables
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
On Error Resume Next
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.PivotCache.Refresh
pt.RefreshTable
On Error GoTo 0
Next
Next

Application.ScreenUpdating = True

Call MsgBox("All done", vbInformation + vbOKOnly, "FixDataAndRefreshPivotTables")

End Sub

jazznaura
08-12-2015, 12:13 AM
Hi Paul,

Sorry for the delay in replying, took a long weekend.
This is great, i agree the pivot looks good and i will be sticking with them.

Again thanks for sticking with this, really appreciated.

J.Naura