Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

Develop function for total time worked

For each "manager" for each "date worked: in a list of 10,000 rows I want to find the maxium time worked for that "date worked" (earliest "start time" minus latest "end time")

Ultimately I need to write the result to a summary array "manager", "helper", "date worked","total time worked" (for the values associated with a manager and the date)

I would like this done in excel in an array if possible.  If done in access I will need additional help to setup, report and graph results.

Thanks in advance for trying.
Sample-Data.xlsx
0
Dallas_Diver
Asked:
Dallas_Diver
  • 6
  • 4
  • 2
1 Solution
 
Jim P.Commented:
Dallas_Diver,

Welcome to EE. Glad to have you on board.

Building this in Access should be fairly easy. And dumping it to an Excel is fairly easy as well. The graphing and the rest is not really my area of expertise though.

A quick question from your sample:
Aaron	Dave 	7:42	10:57	10/4/2012
Aaron	Dave 	10:12	10:59	10/4/2012
Aaron	Dave 	11:51	12:14	10/4/2012
Aaron	Dave 	14:11	14:53	10/4/2012

Open in new window


So in Aaron/Dave combo the earliest  start time 7:42. Would the end time be 14:53 and you would calculate the difference between the two?

Or would it the elapsed time between 7:42 an 10:57, then 11:51 and 12:14, .. and then find the largest of those elapsed times.

Or would it be a total of each row?
0
 
Dallas_DiverAuthor Commented:
Great question! I did that already but adding the elapsed times together only gives me "utilized time" vs "time on-site"  I need total "time on-site" which from my perspective is latest minus earliest time.

Considering a brute force programming approach: I expected the code to look at a manager for each date (the helper is irrelevant) and then within that subset look at the earliest and the latest time to come up with a number of hours worked for that date and write this to a result array.  Then move on to the next manager until all the managers are exhausted for that date.  Once all the managers are written to the result array then move on to the next date... and repeat unitl the result array is complete.

Thx
0
 
byundtCommented:
You can produce the desired report by producing a PivotTable of a PivotTable of your data. See cells W1:AC9 on the attached workbook.

I first turned your raw data into a Table (using the Insert...Table menu item) so it could be used as the source for a PivotTable and automatically adapt to different data.

The first PivotTable should display the Manager, Helper and Date as row items. It should then display the Max of Ending Time and Min of Starting Time as the column items.  I turned subtotals and grand totals off, then used tabular form with row item labels repeated to prepare this PivotTable for the next step.

You then need to copy that PivotTable and Paste Special...Values into a blank range of cells. Once this has been done, convert that into a Table as well.

The final step is to prepare a PivotTable using the copy of the first PivotTable as the source. It should display Manager & Helper as row items, and Date as column item. I used a calculated field equal to 'Max of Ending Time' - 'Min of Starting Time'. Once again, I turned subtotals and grand totals off, then used tabular form with row item labels repeated to look like your desired results. I formatted the calculated field results using a Custom format of hh:mm;;;@

For a task you don't do very often, the manual steps described above go pretty fast. If you have to do it every week, however, you may prefer a macro. If so, please ask.
Sample-DataQ28342319.xlsm
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Jim P.Commented:
So do you want two tables of results then, one with helper one without?

One consideration with Access that I've run into in the past is that it may store a current date  even though you are only enter times in that column. So you may have to clear the dates.

But this query should get you the results you want.
SELECT sub.Manager, sub.Date_worked, sub.StartTm, sub.EndTm, DateDiff("n",[StartTm],[EndTm]) AS ElapsedMin, Int(DateDiff("n",[StartTm],[EndTm])/60) & ":" & (DateDiff("n",[StartTm],[EndTm])) Mod 60 AS ElpsedTm
FROM (SELECT Q28342319.Manager, Q28342319.Date_worked,  cdate( Min( Q28342319.start_time ) ) as StartTm  ,  cdate( Max( Q28342319.end_time) ) as EndTm
FROM Q28342319
group by   Q28342319.Manager, Q28342319.Date_worked)  AS sub;

Open in new window


So you would paste this into the SQL View of a new query window. Change the table/field names as needed.

Then from there the export options vary.

So then what do to do? Same SS every time with a new sheet name? New SS every time? Export to the same sheet? Create a template and copy?
0
 
Dallas_DiverAuthor Commented:
byundt

I may not have been clear but I'm expecting one value for each date for each manager (ignore helper).  If you notice in my example result also shows there is only one date per manager and on that one line there should be a single value for hours worked.

In the second pivot table there are too many lines (see below) .  For example on 10/2/2012 there should be only one value corresponding to Aaron. (there are 2 values).  Also 10/9/2012 there are two values.  Finally the value should be the difference of the largest time - the smallest time.

Sum of MaxMinusMin            Date worked                        
Manager      Helper      10/1/2012      10/2/2012      10/4/2012      10/5/2012      10/9/2012
Aaron      Dave             0:58      7:11      1:28      2:25
Aaron      Hans            1:02                  0:14
Aaron      Steve                        1:14      
Fred      Dave             0:27      6:12      2:05      6:41
Fred      Hans                  5:23      1:50      5:10
Fred      Steve      1:38      0:48      3:21      0:59      2:45
Sarra      Dave                               3:41
0
 
Dallas_DiverAuthor Commented:
Sorry didn't get to say :

Thank you for the creative attempt :)  

I like the apprach if only it had sorted out the values correctly.
Regards
D
0
 
byundtCommented:
Here is code to automate production of the requested report. It will do so on a newly added worksheet called Report. Before running the macro, make sure that the worksheet containing the raw data is active.
Dim PT1Name As String, PT2Name As String, tb1Name As String, tb2Name As String

Sub ProduceReport()
Application.ScreenUpdating = False
Step1
Step2
End Sub

Private Sub Step1()
With ActiveSheet
    tb1Name = "tb1_" & .Name
    PT1Name = "PT1_" & .Name
    .ListObjects.Add(xlSrcRange, .Range("$A$1").CurrentRegion, , xlYes).Name = tb1Name
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=tb1Name).CreatePivotTable _
        TableDestination:=.Name & "!R2C7", TableName:=PT1Name
    With .PivotTables(PT1Name)
        .PivotFields("Manager").Orientation = xlRowField
        .PivotFields("Manager").Position = 1
        .PivotFields("Helper").Orientation = xlRowField
        .PivotFields("Helper").Position = 2
        .PivotFields("Date worked").Orientation = xlRowField
        .PivotFields("Date worked").Position = 3
    
        .AddDataField .PivotFields("end time"), "Max of end time", xlMax
        .AddDataField .PivotFields("start time"), "Min of start time", xlMin
        .PivotFields("Manager").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("Helper").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("start time").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("end time").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("Date worked").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        
        .ColumnGrand = False
        .RowGrand = False
        .RowAxisLayout xlTabularRow
        .RepeatAllLabels xlRepeatLabels
    End With
End With
End Sub

Private Sub Step2()
Dim rgPT1 As Range, rgTable1 As Range
Dim PT As PivotTable
With ActiveSheet
    Set rgPT1 = .PivotTables(PT1Name).TableRange1
    Set rgPT1 = rgPT1.Offset(1, 0).Resize(rgPT1.Rows.Count - 1)
    rgPT1.Copy
    .Range("M2").PasteSpecial Paste:=xlPasteValues
    
    tb2Name = "tb2_" & .Name
    .ListObjects.Add(xlSrcRange, .Range("M2").CurrentRegion, , xlYes).Name = tb2Name
    Sheets.Add After:=Worksheets(.Index)
End With
    
With ActiveSheet
    .Name = "Report"
    PT2Name = "PT2_" & .Name
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=tb2Name).CreatePivotTable _
        TableDestination:=.Name & "!R3C1", TableName:=PT2Name
    ActiveWorkbook.ShowPivotTableFieldList = True
    With .PivotTables(PT2Name)
        .PivotFields("Manager").Orientation = xlRowField
        .PivotFields("Manager").Position = 1
        .PivotFields("Helper").Orientation = xlRowField
        .PivotFields("Helper").Position = 2
        .PivotFields("Date worked").Orientation = xlColumnField
        .PivotFields("Date worked").Position = 1
    
        .CalculatedFields.Add "MaxMinusMin", "='Max of end time' -'Min of start time'", True
        .PivotFields("MaxMinusMin").Orientation = xlDataField
        .PivotFields("Manager").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("Helper").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("Date worked").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("Max of end time").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("Min of start time").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        
        .ColumnGrand = False
        .RowGrand = False
        .RowAxisLayout xlTabularRow
        .RepeatAllLabels xlRepeatLabels
        
        .ColumnRange.NumberFormat = "m/d/yyyy"
        .DataBodyRange.NumberFormat = "h:mm;;;@"
    End With

End With
End Sub

Open in new window

Sample-DataQ28342319.xlsm
0
 
byundtCommented:
I revised the code to eliminate the Helper column and to sort the results by date rather than Manager name.
Dim PT1Name As String, PT2Name As String, tb1Name As String, tb2Name As String

Sub ProduceReport()
Application.ScreenUpdating = False
Step1
Step2
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

Private Sub Step1()
With ActiveSheet
    tb1Name = "tb1_" & .Name
    PT1Name = "PT1_" & .Name
    .ListObjects.Add(xlSrcRange, .Range("$A$1").CurrentRegion, , xlYes).Name = tb1Name
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=tb1Name).CreatePivotTable _
        TableDestination:=.Name & "!R2C7", TableName:=PT1Name
    With .PivotTables(PT1Name)
        .PivotFields("Manager").Orientation = xlRowField
        .PivotFields("Manager").Position = 1
        '.PivotFields("Helper").Orientation = xlRowField
        '.PivotFields("Helper").Position = 2
        .PivotFields("Date worked").Orientation = xlRowField
        .PivotFields("Date worked").Position = 2
    
        .AddDataField .PivotFields("end time"), "Max of end time", xlMax
        .AddDataField .PivotFields("start time"), "Min of start time", xlMin
        .PivotFields("Manager").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        '.PivotFields("Helper").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("start time").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("end time").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("Date worked").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        
        .ColumnGrand = False
        .RowGrand = False
        .RowAxisLayout xlTabularRow
        .RepeatAllLabels xlRepeatLabels
    End With
End With
End Sub

Private Sub Step2()
Dim rgPT1 As Range, rgTable1 As Range
Dim PT As PivotTable
With ActiveSheet
    Set rgPT1 = .PivotTables(PT1Name).TableRange1
    Set rgPT1 = rgPT1.Offset(1, 0).Resize(rgPT1.Rows.Count - 1)
    rgPT1.Copy
    .Range("M2").PasteSpecial Paste:=xlPasteValues
    
    tb2Name = "tb2_" & .Name
    .ListObjects.Add(xlSrcRange, .Range("M2").CurrentRegion, , xlYes).Name = tb2Name
    Sheets.Add After:=Worksheets(.Index)
End With
    
With ActiveSheet
    .Name = "Report"
    PT2Name = "PT2_" & .Name
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=tb2Name).CreatePivotTable _
        TableDestination:=.Name & "!R3C1", TableName:=PT2Name
    ActiveWorkbook.ShowPivotTableFieldList = True
    With .PivotTables(PT2Name)
        .PivotFields("Date worked").Orientation = xlRowField
        .PivotFields("Date worked").Position = 1
        .PivotFields("Manager").Orientation = xlRowField
        .PivotFields("Manager").Position = 2
        '.PivotFields("Helper").Orientation = xlRowField
        '.PivotFields("Helper").Position = 2
    
        .CalculatedFields.Add "MaxMinusMin", "='Max of end time' -'Min of start time'", True
        .PivotFields("MaxMinusMin").Orientation = xlDataField
        .PivotFields("Manager").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        '.PivotFields("Helper").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("Date worked").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("Max of end time").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("Min of start time").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
        
        .ColumnGrand = False
        .RowGrand = False
        .RowAxisLayout xlTabularRow
        .RepeatAllLabels xlRepeatLabels
        
        .TableRange1.Columns(1).NumberFormat = "m/d/yyyy"
        .DataBodyRange.NumberFormat = "h:mm;;;@"
    End With
End With
End Sub

Open in new window

Sample-DataQ28342319.xlsm
0
 
Dallas_DiverAuthor Commented:
Great!
Yes that Works appears to work for the sample data...

But I'm not an excel power user

Please support the solution approach with summary on how to best use macro and or the files so I make practical use of the solution.

Do I create a specific file named "x" or just paste into a certain location etc?

Thank you,
In advance.
DD
0
 
Dallas_DiverAuthor Commented:
byundt,

I'm using the file you sent... attempting to reproduce what you build.   When I attempt to select table1 or table2 to generate a resultant pivot table via "table tools"  I get a "Reference is not valid" dialog.  1-19-2014-12-00-47-PM.pdf

Thank you,
G
0
 
byundtCommented:
To use the macro:
1.  Put your data (including header labels) in a blank worksheet, with first header label in cell A1
2.  ALT + F8 to display the macro selector
3.  Select the ProduceReport macro, then click Run button

The macro does not need to be installed in the same workbook as the data. When you do install the macro, it should be in a regular module sheet (just like I did in the sample workbook I posted). The workbook containing the macro must be saved with .xlsm file extension.

Note that you will get a runtime error if the data in step 1 is already in a Table when you run the macro.

I gave an overview rather than step by step instructions for the manual method because I wasn't sure if that was what you wanted. And now that I have written the macro to automate the process, using the macro is a lot easier workflow.
0
 
Dallas_DiverAuthor Commented:
Byundt,
Thank you for your solution approach and your patience in explaining .  
Regards,
DD
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now