Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for hours on time sheet
I have a time sheet that calculates start time and end time, neither of which
could be constant times. That formula works fine. I am looking to take the total hours for any given day and break them down into regular hours (upto and including 8 hrs) and overtime pay (anything over 8 hours). The problem is that some days there might not be a full 8 hrs put in and then the overtime hours result in an error. The way I have it set up now is for OT to subtract 8hrs from the Total hrs. and then the Regular time subtracts the OT hours (or minutes) from the Total time. How can I tell the OT formulat to do what I told it to but if the answer is a negative number to just enter '0'? Or is there an easier solution??? Can anyone assist me???? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for hours on time sheet
Simply check to see if the hours worked is greater than 8. Something like:
=if(a2-a18,a2-a1-8,0) Regards Fred "Matt" wrote in message ... I have a time sheet that calculates start time and end time, neither of which could be constant times. That formula works fine. I am looking to take the total hours for any given day and break them down into regular hours (upto and including 8 hrs) and overtime pay (anything over 8 hours). The problem is that some days there might not be a full 8 hrs put in and then the overtime hours result in an error. The way I have it set up now is for OT to subtract 8hrs from the Total hrs. and then the Regular time subtracts the OT hours (or minutes) from the Total time. How can I tell the OT formulat to do what I told it to but if the answer is a negative number to just enter '0'? Or is there an easier solution??? Can anyone assist me???? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for hours on time sheet
Thanks Fred, but unfortunately that left me with a perminant '0'.
What I have is: Time in 7:00 (D4) Time out 15:00 (E4) Total time 8:00 (F4) Regular time 8:00 (G4) Overtime 0:00 (H4) However sometimes there could be a short day or a longer day it works fine if the time out is 15:01 or later. Total time is 8:01, Regular Time is 8:00, and Overtime is 0:01. But, when it is a short day, say Time out is 13:00, Total time reads 6:00, Overtime goes all ####### and Regular time is 0:00. "Fred Smith" wrote: Simply check to see if the hours worked is greater than 8. Something like: =if(a2-a18,a2-a1-8,0) Regards Fred "Matt" wrote in message ... I have a time sheet that calculates start time and end time, neither of which could be constant times. That formula works fine. I am looking to take the total hours for any given day and break them down into regular hours (upto and including 8 hrs) and overtime pay (anything over 8 hours). The problem is that some days there might not be a full 8 hrs put in and then the overtime hours result in an error. The way I have it set up now is for OT to subtract 8hrs from the Total hrs. and then the Regular time subtracts the OT hours (or minutes) from the Total time. How can I tell the OT formulat to do what I told it to but if the answer is a negative number to just enter '0'? Or is there an easier solution??? Can anyone assist me???? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for hours on time sheet
The following formula always returns zero if the answer would otherwise be
negative. =MAX(A1-B1,0) -- Regards, OssieMac "Matt" wrote: I have a time sheet that calculates start time and end time, neither of which could be constant times. That formula works fine. I am looking to take the total hours for any given day and break them down into regular hours (upto and including 8 hrs) and overtime pay (anything over 8 hours). The problem is that some days there might not be a full 8 hrs put in and then the overtime hours result in an error. The way I have it set up now is for OT to subtract 8hrs from the Total hrs. and then the Regular time subtracts the OT hours (or minutes) from the Total time. How can I tell the OT formulat to do what I told it to but if the answer is a negative number to just enter '0'? Or is there an easier solution??? Can anyone assist me???? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for hours on time sheet
It would help if you told us what formulas you are using. Without them, we
can only guess what's happening. So I'll take a guess that you are using Excel times for everything. In that case, you need to check for a time rather than a number. So: Regular time, G4: =min(f4,time(8,0,0)) Overtime, H4: =if(f4time(8,0,0),f4-time(8,0,0),0) Regards, Fred. "Matt" wrote in message ... Thanks Fred, but unfortunately that left me with a perminant '0'. What I have is: Time in 7:00 (D4) Time out 15:00 (E4) Total time 8:00 (F4) Regular time 8:00 (G4) Overtime 0:00 (H4) However sometimes there could be a short day or a longer day it works fine if the time out is 15:01 or later. Total time is 8:01, Regular Time is 8:00, and Overtime is 0:01. But, when it is a short day, say Time out is 13:00, Total time reads 6:00, Overtime goes all ####### and Regular time is 0:00. "Fred Smith" wrote: Simply check to see if the hours worked is greater than 8. Something like: =if(a2-a18,a2-a1-8,0) Regards Fred "Matt" wrote in message ... I have a time sheet that calculates start time and end time, neither of which could be constant times. That formula works fine. I am looking to take the total hours for any given day and break them down into regular hours (upto and including 8 hrs) and overtime pay (anything over 8 hours). The problem is that some days there might not be a full 8 hrs put in and then the overtime hours result in an error. The way I have it set up now is for OT to subtract 8hrs from the Total hrs. and then the Regular time subtracts the OT hours (or minutes) from the Total time. How can I tell the OT formulat to do what I told it to but if the answer is a negative number to just enter '0'? Or is there an easier solution??? Can anyone assist me???? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I time Hours & mins in excel - Time sheet | Excel Discussion (Misc queries) | |||
Time Sheet to show hours owed | Excel Worksheet Functions | |||
Is there a time sheet template that calculates hours? | Excel Discussion (Misc queries) | |||
Is there a time sheet template that calculates hours? | Excel Discussion (Misc queries) | |||
time-sheet record with over 24 hours | New Users to Excel |