Here are a few hints for a solution that breaks the task down to manageable
bits -- let's say the employee's time of arrival is stored in a cell named
"ArriveTime", the time he/she left in "LeaveTime", and the beginning and end
of regular working hours are stored in "RTstart" and "RTend" respectively.
If someone arrives earlier than beginning of regular working hours (i.e.
ArriveTime < RTstart), there's some overtime. We can use the IF worksheet
function to figure out the number of overtime hours in early morning:
= IF(ArriveTime<RTstart ; (RTstart-ArriveTime)*24 ; 0)
If the condition "ArriveTime<RTstart" is met, this formula works out how
many hours overtime that makes for in the morning (the factor 24 is necessary
to convert the decimal days that Excel works with, to the hour count we'd
like). If the condition isn't met, it returns the zero I've put near the end
of the formula; zero morning overtime hours for those who arrive at regular
time start (or later!).
We can do a similar trick for those who stay to after the end of regular time:
= IF(LeaveTime>RTend ; (LeaveTime-RTstart)*24 ; 0)
Now, add the results of the two formulae I've written here, and you've got
the amount of overtime hours for that employee that day.
Now let's look at how many of the regular working hours the employee is
present: If an employee arrives before regular hours, we've already worked
out their overtime; their "chunk" of regular working hour that day still
starts at the regular hours' start. For those arriving later, their regular
hours start whenever they arrive. So, it's a whatever-comes-last-situation;
regular hours or actual arrival time. We can use the MAX worksheet function
to determine this:
= MAX(ArriveTime ; RTstart)
At the end of the day, the reverse applies: The chunk of regular hours ends
a regular time end, or when the employee leaves, whichever comes first. So
the formula is:
= MIN(LeaveTime ; RTend)
Now we can figure out how many work hours that employee put in within that
regular time "window" by simply subtracting the result of the latter from the
result of the former.
This is just a basic "sketch" of a solution -- I haven't accounted for that
half-hour lunchbreak you mentioned, and if someone is at work by midnight the
method presented here will fail, but hopefully you've gleaned a few tips and
ideas on how to make it work just like you want it.
