1

I have a .XLXS table with worker names, start times, and end times. I want to show the total hours worked by worker while ignoring overlapping periods. For example if "Bob" works from 0800 to 1400 and also works from 0900 to 1300 his total time for that day should be 6 hours. If "Joe" works 0700 to 0900 his time for that day is 2 hours.

The number of workers varies and six months of data is about 2100 rows.

The formula provided by "barry houdini" to "Formula that will calculate total hours in overlapping date array" works for one worker but I don't know enough about Excel to make it sort for multiple workers.

=SUMPRODUCT((COUNTIFS(B:B,"<"&MIN(B:B)+ROW(INDIRECT("1:"&ROUND((MAX(C:C)-MIN(B:B))*1440,0)))/1440-1/2880,C:C,">"&MIN(B:B)+ROW(INDIRECT("1:"&ROUND((MAX(C:C)-MIN(B:B))*1440,0)))/1440-1/2880)>0)+0)/60


MOD_BY  START_DATETIME  END_DATETIME        total time  
bob     1/2/19 17:30    1/2/19 18:45        3.2500  
bob     1/2/19 21:00    1/2/19 21:15            
bob     1/2/19 21:00    1/2/19 22:00            
bob     1/2/19 15:00    1/2/2019 16:00          
joe     1/2/19 17:30    1/2/19 18:45            
joe     1/2/19 21:00    1/2/19 21:15            
joe     1/2/19 21:00    1/2/19 22:00            
joe     1/2/19 15:00    1/2/2019 16:00

My objective is to have an output of total hours worked by worker. The formula as provided gives a total for the entire file without breaking it down by worker.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Morgan Ford
  • 11
  • 2
  • You have to change the `Ranges` in which the data for a person is present. Like in the formula above you need to change `B:B` to `B2:B6` if data for Bob is in rows 2 to 6. Similarly with other columns like C or nay other present in Formula – Mikku Jun 14 '19 at 04:08
  • Might be worth comparing my answer https://stackoverflow.com/questions/53572815/duration-and-idle-time-for-a-server-from-continuous-dates/53579235#53579235 which does require a helper column but doesn't need 365. – Tom Sharpe Jun 14 '19 at 07:18

1 Answers1

1

You will need MAXIFS and MINIFS which means you need Office 365 Excel:

=SUMPRODUCT(--(COUNTIFS(A:A,"bob",B:B,"<=" & MINIFS(B:B,A:A,"bob") + ROW($XFD$1:INDEX($XFD:$XFD,ROUND((MAXIFS(C:C,A:A,"bob")-MINIFS(B:B,A:A,"bob"))*1440,0)))/1440-1/2880,C:C,">=" & MINIFS(B:B,A:A,"bob") + ROW($XFD$1:INDEX($XFD:$XFD,ROUND((MAXIFS(C:C,A:A,"bob")-MINIFS(B:B,A:A,"bob"))*1440,0)))/1440-1/2880)>0))/60

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • I got to work by sorting each worker to a separate sheet. Excel froze up on the formula so I uploaded to Google sheets and ran the calculation there. – Morgan Ford Jun 19 '19 at 03:13