View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
E-QUIP E-QUIP is offline
external usenet poster
 
Posts: 12
Default CALCULATE OT AND DT

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.