I have a time sheet form that calculates regular time,over time and holiday
time. I can't seem to get the formulas correct.
Would anyone look at it and give me some advice?
It is only 17K
I tried to copy it to this message but it was very confusing

oldjay

I'll give it a go - just don't expect miracles?
attach to email and send to

"Oldjay" wrote:

I have a time sheet form that calculates regular time,over time and holiday
time. I can't seem to get the formulas correct.
Would anyone look at it and give me some advice?
It is only 17K
I tried to copy it to this message but it was very confusing

oldjay

Thanks JLatham for the reply but I was looking for a solution before you get
back on Oct 8
Can anybody else look at my problem?

oldjay

"Oldjay" wrote:

I have a time sheet form that calculates regular time,over time and holiday
time. I can't seem to get the formulas correct.
Would anyone look at it and give me some advice?
It is only 17K
I tried to copy it to this message but it was very confusing

oldjay

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
on that right now.





"Oldjay" wrote:

Thanks JLatham for the reply but I was looking for a solution before you get
back on Oct 8
Can anybody else look at my problem?

oldjay

"Oldjay" wrote:

I have a time sheet form that calculates regular time,over time and holiday
time. I can't seem to get the formulas correct.
Would anyone look at it and give me some advice?
It is only 17K
I tried to copy it to this message but it was very confusing

oldjay

Two possible formulas to use in column I, starting at row 3 a

=IF(C3="Y",0,IF(D3$E$24,0,MIN(H3,8)))
handles the case where ONLY shifts starting after 5:00 PM (in $E$24) get
time-and-a-half rate, and
=IF(C3="Y",0,IF(OR(D3<$E$23,D3$E$24),0,MIN(H3,8)) )
which handles situation where shift starting before 8:00 AM (in $E$23) also
gets overtime rate.

Question remaining seems to be if a shift starts before 8:00 AM, do they get
1.5 overtime rate for the entire shift, or just until 8:00 AM rolls around?

Working on a solution for that while waiting for the answer that I'm pretty
sure is going to come: only pay 1.5x rate until 8:00 AM



"Oldjay" wrote:

Thanks JLatham for the reply but I was looking for a solution before you get
back on Oct 8
Can anybody else look at my problem?

oldjay

"Oldjay" wrote:

I have a time sheet form that calculates regular time,over time and holiday
time. I can't seem to get the formulas correct.
Would anyone look at it and give me some advice?
It is only 17K
I tried to copy it to this message but it was very confusing

oldjay

Final update:
In the end we realized that a different split of the workload on the sheet
was needed. Total hours was being calculated, but overtime hours hadn't been
given close enough consideration. A MAX() formula was used with 4 arguments,
one for each possible overtime scenario:
shift began before normal working hours, the time before 8 a.m. is overtime
shift ended after normal working hours, time after 5 p.m. is overtime
shift longer than 8 hours, all over 8 is overtime
entire shift took place outside of normal working hours: all time is overtime

At that time, what had previously been a complex calculation for regular
hours became incredibly simple: regular hours = total hours - overtime hours.

Solution provided. Oldjay seems happy with it.

"Oldjay" wrote:

Thanks JLatham for the reply but I was looking for a solution before you get
back on Oct 8
Can anybody else look at my problem?

oldjay

"Oldjay" wrote:

I have a time sheet form that calculates regular time,over time and holiday
time. I can't seem to get the formulas correct.
Would anyone look at it and give me some advice?
It is only 17K
I tried to copy it to this message but it was very confusing

oldjay

Thanks JLatham Your final solution works perfectly

oldjay

"Oldjay" wrote:

Thanks JLatham for the reply but I was looking for a solution before you get
back on Oct 8
Can anybody else look at my problem?

oldjay

"Oldjay" wrote:

I have a time sheet form that calculates regular time,over time and holiday
time. I can't seem to get the formulas correct.
Would anyone look at it and give me some advice?
It is only 17K
I tried to copy it to this message but it was very confusing

oldjay

Glad to hear it - and now we can both fully enjoy the upcoming holiday! <g

"Oldjay" wrote:

Thanks JLatham Your final solution works perfectly

oldjay

"Oldjay" wrote:

Thanks JLatham for the reply but I was looking for a solution before you get
back on Oct 8
Can anybody else look at my problem?

oldjay

"Oldjay" wrote:

I have a time sheet form that calculates regular time,over time and holiday
time. I can't seem to get the formulas correct.
Would anyone look at it and give me some advice?
It is only 17K
I tried to copy it to this message but it was very confusing

oldjay