Oldjay,
In my email response to you I was just kidding about October 9 - I had
question in my mind on how you handle holidays - that's been answered, they
are clearly marked - and October 9th is next holiday date I could have used
to test (just kidding)
for those interested in trying to help and perhaps coming up with a solution
before I do, here is the general layout and formulas used for days of the
week M-F (Sat and Sunday are handled effectively already:
Data entry starts in row 3
Column C is holiday indicator (Y or blank)
Column D is work start time as 8:00 AM
Column E is End Work/Start lunch entry same format as D
Column F is return to work time after lunch, again same format as D
Column G is end of second work period time, again in same format as D
Total hours are calculated in H3 as =((E3<D3)+E3-D3+(G3<F3)+G3-F3)*24
we will skip column I (Regular Hours) for the moment, that's where the
question/issue is
Column J is Time-and-a-Half Hours as: =IF(C3="y",0,H3-I3)
Column K is Double-Time Hours as: =IF(C3="Y",H3,0)
Basic rules are all Holidays and Sundays are double-time.
All time over 8 hours/day is time-and-a-half
Shift start after 5:00 PM is time-and-a-half
As I said, issue is back in column I, calculating regular hours. Initially
the formula was entered as
=IF(C3="y",0,IF(D3>$E$24,0,IF(H3-IF(D3<$E$23,(D3-$E$23)*24,0)+IF(G3>$E$24,($E$24-G3)*24,0)>8,8)))
That occassionally gave entry of FALSE, but changing the end of it from
">8,8)))" to ">8,8,0))) cured that problem.
$E$23 contains normal start time of 8:00 AM and $E$24 contains overtime
start time of 5:00 PM
It *may* be working just fine now, double checking things, but looking at
simplifying the complex formula in I3 to use a MIN formula like:
=IF(C3="Y",0,IF(D3>$E$24,0,MIN(H3,8)))
However, that may be slightly incorrect in that if a shift starts before
regular start time of 8:00 AM (in $E$23) that may be a time-and-a-half shift,
just as if it had started after 5:00 PM. I'm getting a reading from Oldjay
