Wednesday, April 13, 2011

yogi_Compute HoursWorked EarliestStart LatestFinish By Day


Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Salamosam said:
In an employee's time log, how do I calculate the duration of a single day using the start/finish times in diff. cols??

Employee Time Log

Date StartTime FinishTime Duration CumulativeDuration MinStart MaxFinish
2/11      8:30      12:00      3.5                3.5     8:30
2/11     12:00       2:00      2                  5.5
2/11      2:00       4:00      2                  7.5               4:00
2/12      9:00      11:00      2                  2       9:00
2/12     11:00      12:00      1                  3
2/12      1:00       4:00      3                  6
2/12      4:00       5:00      1                  7                 5:00
2/13      9:00      12:00      3                  3       9:00
2/13     12:00       4:00      4                  7                 4:00

I got this far, that is I can find the cumulative duration for each day. But I want to do the calculation another way to check for errors. I want to find the max finish time of one day and the min start time for the same day and find the difference. I can find the min and max times I'm looking for, but they are in different columns and uneven rows (see Min Start and Max Finish), and I don't know how to manipulate them to make the calculation.



In the following solution, I have used Military Time (24 hour clock) to avoid any ambiguity about morning (AM) time or afternoon (PM) time.