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.
No comments:
Post a Comment