I am using excel and I want to put in a start and end time and i want it to
automatically calculate reg ot and dt..how do i do that? can anyone help?

E-QUIP wrote:
I am using excel and I want to put in a start and end time and i want it to
automatically calculate reg ot and dt..how do i do that? can anyone help?

The exact formula will depend on the jurisdiction and perhaps company
policy to some degree.

But for example, suppose that you should be paid "time and a half" for
time exceeding 8 hours in a day (including weekends), and suppose you
should be paid "double time" for weekends (which I will define as
Saturday and Sunday).

Note: Some jurisdictions have much more complex overtime rules,
sometimes depending on total hours worked in a week. Even the
definition of the word "week" depends on the jurisdiction.

Suppose that A1 is your hourly base rate, A2 is the date, and B2 and C2
are the start and end times in the form 8:30 am and 4:30 pm. Then your
wages for the day might be computed as follows:

=round( if(weekday(A2,2) 5, 2*$A$1, $A$1) * ( 24*(C2 - B2) +
0.5*max(0, 24*(C2-B2) - 8) ), 2 )

Caveat: That formula assumes that the shift begins and ends on the
same day. It also assumes that you are paid by the minute. Sometimes,
pay is based on multiples of 15 or 30 minutes. Finally, the formula
assumes that in your jurisdiction, you are paid for lunch and other
breaks.

I am trying to create a workorder on that work order I want to enter a start
time end time and lunch (if a lunch is taken i will enter the amount taken
for lunch). Our time is based on multiples of 15/30 minutes our reg hours
are 7:00am to 3:30pm anytime before or after is OT M-F (Sat OT & Sun DT)
What i want it to do is after i enter the start and end time i want it to
calculate what is REG, OT and DT taking out if any lunch time. I hope this
makes sense to you and mostly I hope you can help me.

" wrote:

E-QUIP wrote:
I am using excel and I want to put in a start and end time and i want it to
automatically calculate reg ot and dt..how do i do that? can anyone help?

The exact formula will depend on the jurisdiction and perhaps company
policy to some degree.

But for example, suppose that you should be paid "time and a half" for
time exceeding 8 hours in a day (including weekends), and suppose you
should be paid "double time" for weekends (which I will define as
Saturday and Sunday).

Note: Some jurisdictions have much more complex overtime rules,
sometimes depending on total hours worked in a week. Even the
definition of the word "week" depends on the jurisdiction.

Suppose that A1 is your hourly base rate, A2 is the date, and B2 and C2
are the start and end times in the form 8:30 am and 4:30 pm. Then your
wages for the day might be computed as follows:

=round( if(weekday(A2,2) 5, 2*$A$1, $A$1) * ( 24*(C2 - B2) +
0.5*max(0, 24*(C2-B2) - 8) ), 2 )

Caveat: That formula assumes that the shift begins and ends on the
same day. It also assumes that you are paid by the minute. Sometimes,
pay is based on multiples of 15 or 30 minutes. Finally, the formula
assumes that in your jurisdiction, you are paid for lunch and other
breaks.

E-QUIP wrote:
I am trying to create a workorder on that work order I want to enter a start
time end time and lunch (if a lunch is taken i will enter the amount taken
for lunch). Our time is based on multiples of 15/30 minutes our reg hours
are 7:00am to 3:30pm anytime before or after is OT M-F (Sat OT & Sun DT)
What i want it to do is after i enter the start and end time i want it to
calculate what is REG, OT and DT taking out if any lunch time. I hope this
makes sense to you and mostly I hope you can help me.

We can probably get you started; but the devil is in the details. For
example, when you say "multiples of 15 min", is the time rounded up or
down? Also, does lunch count as regular time or overtime when it is
taken on "regular" days?

I'm gonna play Scrooge and make the following assumptions:
1. Work time is rounded down to 15-min increments.
2. Lunch time is rounded up to 15-min increments.
3. If lunch starts or ends during any work hours, work time is reduced
by the entire lunch time, even if part of lunch time is outside of work
hours.
4. If lunch starts or ends during overtime hours, overtime is reduced
by the entire lunch time (unless overtime rounds to zero), even if part
of lunch time is during regular hours.
5. There is no overtime on "double time" days.
6. The shift starts and ends on the same day (and end before midnight).

Moreover, I assume now that you only want the number of Reg, OT and DT
hours, not the wages as I had assumed in my previous posting.

Assume the following spreadsheet design:

A1: Reg Time
B1: 7:00 am
C1: 3:30 pm

A2: Date
B2: Start Time
C2: End Time
D2: Lunch Hrs
E2: Reg Hrs
F2: OT Hrs
G2: DT Hrs

If you enter the daily information in A3, B3, C3 and D3 (for example),
then E3, F3 and G3 might be computed as follows, simplifying the rules
above somewhat:

E3: =(weekday(A3,2)<=5) * max(0, floor(24*(min(C3,$C$1) -
max(B3,$B$1)), 0.25) - and($B$1<=B3, C3<=$C$1)*ceiling(D3, 0.25))

F3: =(weekday(A3,2)<=6) * max(0, floor(24*(C3-B3), 0.25) - E3 -
ceiling(D3, 0.25))

G3: =(weekday(A3,2)=7) * max(0, floor(24*(C3-B3), 0.25) - ceiling(D3,
0.25))

These formulas simplify rule #4 above as follows: lunch time reduces
overtime hours, unless overtime is zero, in which case lunch time
reduces regular hours. That is probably unduly conservative and
unrealistic; but it makes the formulation much more tractable.

The floor(24*...,0.25) function rounds down to increments of a
quarter-hour (15 min), whereas ceiling(...,0.25) rounds up to
increments of a quarter-hour.

The min(...)-max(...) expression computes the amount of the work time
that was performed during regular hours.

The weekday(...,2) function returns 1-5 for Mon-Fri, 6 for Sat, and 7
for Sun.

Although it might seem like the formula in F3 is double-accounting for
lunch time, it is not. In E3, the and(...) function ensures that lunch
time is subtracted from regular hours only if the entire work time is
between regular hours. This is the simplification mentioned above.

If the simplification bothers you, I could provide formulas that
implement the rules above more precisely. Alternatively, I could
modify the formulas above (if you cannot) so that the simplification
goes in the worker's favor, counting the entire lunch time against
regular hours, not overtime.

I believe rule #6 (same-day shift) can be avoided by changing the input
data and modifying the formulas somewhat.

HTH. It is not intended to be a turn-key solution. It is only
intended to point you in the right direction. "Some assembly required"
;-).