Solved

Develop function for total time worked

Posted on 2014-01-18
12
336 Views
Last Modified: 2014-01-19
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
Comment
Question by:Dallas_Diver
  • 6
  • 4
  • 2
12 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 39791601
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
 

Author Comment

by:Dallas_Diver
ID: 39791648
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
 
LVL 80

Expert Comment

by:byundt
ID: 39791673
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39791682
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
 

Author Comment

by:Dallas_Diver
ID: 39791729
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
 

Author Comment

by:Dallas_Diver
ID: 39791731
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 80

Expert Comment

by:byundt
ID: 39791736
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
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39791748
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
 

Author Comment

by:Dallas_Diver
ID: 39792433
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
 

Author Comment

by:Dallas_Diver
ID: 39792488
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
 
LVL 80

Expert Comment

by:byundt
ID: 39792572
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
 

Author Comment

by:Dallas_Diver
ID: 39792719
Byundt,
Thank you for your solution approach and your patience in explaining .  
Regards,
DD
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now