Sum time in a period

[Jordo82]

I'm working on what is essentially a timesheet to help a friend calculate expected pay, but I've run into a roadblock in one area and would appreciate some help.

I have a date/time when she punches in and another when she punches out. Calculating total time spent on the job is easy, but I also need to know how many hours (if any) were spent working during a certain "golden time" period of the day. To make matters more complicated, this period is typically 11PM - 5AM, so the overnight issue complicates things.

For example, if she punches in at 7PM and punches out at 7AM the next day, she worked 12 hours total and 6 hours in the period 11AM - 5AM. If she works 12PM to 12AM, there's only 1 hour of "golden time". Essentially I want to sum the intersection of hours worked and the "golden time" period.

For optimum flexibility, I'd like to avoid hardcoding the definition of the "golden time" period. There's also the potential for 18 or 24 hour shifts so there may be multiple passes through the "golden time" period. I realize this may be more of a math problem than an Excel problem, but any help would be appreciated. Thanks!

 

[barry houdini]

Assuming you have a start time in A2 and an end time in B2 this formula will give you the total hours in a period defined in J2:J3 (earlier time must be in J2)

=(A2>B2)*MEDIAN(0,B2-J$2,J$3-J$2)+MAX(0,MIN(J$3,B2+(A2>B2))-MAX(J$2,A2))

so for your scenario you can put 05:00 in J2 and 23:00 in J3 and that formula will calculate the time worked within that period. Subtract that amount from the total hours and you'll get the time worked in 23:00 to 05:00.

If you want that all in one formula then you can use this version

=MOD(B2-A2,1)-((A2>B2)*MEDIAN(0,B2-J$2,J$3-J$2)+MAX(0,MIN(J$3,B2+(A2>B2))-MAX(J$2,A2)))

All result cells should be formatted as time. Works for shifts up to 24 hours

 

[Jordo82]

Barry, brilliant! Thanks very much for your quick reply, I was really stumped with that one.