Page 1 of 4 1 2 3 ... LastLast
Results 1 to 10 of 38

Thread: Formula for tracking my hours worked including overtime etc

  1. #1

    Cool Formula for tracking my hours worked including overtime etc



    Register for a FREE account, and/
    or Log in to avoid these ads!

    If anyone is able to create a formula for me that would be able to calculate how many hours are regular time up to a max and how many hours are at overtime. I have a sheet that calculates most things for me already but I would like to be able to just key in for example that I started at 5:30 am and finished at 8:30pm and the formula would be able to figure out what was regular time and what was overtime. Theres more details than this to the formula but this is basically what im trying to find.

  2. #2
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Start Time in Cell A2, Finish Time in Cell B2 and Assume Regular Time is 8 Hrs. Try below formula.

    C2 Cell is Regular Time =IF(((B2-A2)*24)>8,8,((B2-A2)*24))
    D2 Cell is Over Time =IF(((B2-A2)*24)>8,((B2-A2)*24-8))
    Attached Files Attached Files

  3. #3

    Thats awesome thank you

    Thank you, actually our regular hours are 10 hours then anything over 10 is over time. However its a bit more tricky than that as well. i'll try to explain.
    For the first 4 days of the week, the first 10 hours of each shift is regular time, then everything after that initial 10 is over time.
    On the fifth shift of the week the first 4 hours of the shift is Reg time and then everything else is OT.
    On the 6 and 7 shift of the week it straight overtime
    eg.
    Sunday 5:00 am-7:00 pm = 10 reg and 4 OT
    Monday 5:00 am-7:00 pm = 10 reg and 4 OT
    Tuesday 5:00 am-7:00 pm = 10 reg and 4 OT
    Wednesday 5:00 am-7:00 pm = 10 reg and 4 OT
    Thursday 5:00 am-7:00 pm = 4 reg and 10 OT
    Friday 5:00 am-7:00 pm = 14 OT
    Saturday 5:00 am-7:00 pm = 14 OT

    I would gladly pay you if you can figure this out.

    [QUOTE=sambit;17947]Start Time in Cell A2, Finish Time in Cell B2 and Assume Regular Time is 8 Hrs. Try below formula.

    C2 Cell is Regular Time =IF(((B2-A2)*24)>8,8,((B2-A2)*24))
    D2 Cell is Over Time =IF(((B2-A2)*24)>8,((B2-A2)*24-8))

  4. #4
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi,
    Plz see the file.
    Attached Files Attached Files

  5. #5

    WOW Thank you

    Quote Originally Posted by sambit View Post
    Hi,
    Plz see the file.

    Hi Sambit, im just reviewing your formula. WOW I seriously need to take a class on this. However there is one problem. Monday doesnt seem to work, When I change the times on tuesday it changes the calculated hours on monday. It seems to do this the rest of the way down the week. also lets say if I didnt work a 10 hour shift on one of those first few days would it know to make the THURSDAY for example more regular hours until I hit the 44 reg hours before it put the rest into Overtime.

    Sorry it seems like im being pciky Im definately not and I do very much appreciate the time and effort you put into this, as I said before I would gladly pay you. Thanks

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    Regular hours

    =IF(OR($A2="FRI",$A2="SAT"),0,IF($A2="THU",MIN(($C2-$B2)*24,4),MIN(($C2-$B2)*24,10)))

    Overtime hours

    =($C2-$B2)*24-$D2

  7. #7
    Quote Originally Posted by Bob Phillips View Post
    Regular hours

    =IF(OR($A2="FRI",$A2="SAT"),0,IF($A2="THU",MIN(($C2-$B2)*24,4),MIN(($C2-$B2)*24,10)))

    Overtime hours

    =($C2-$B2)*24-$D2
    Could I ask for another revision to this if possible. We do a lot of 15-16 hour days. But the way it works is the first 10 hours of your first 4 shifts are regular time. Anything over that 10 hours is overtime. On the fifth shift the first 4 hours is regular time then everything after that is Overtime. On the 6 and 7 shift everything is straight overtime. However if at some point in the first few shift we dont do our 10 hours of reg time then it pushs everything else back so essential we would have to make it up on the fifth shift where the 4 hours of regular time are before the overtime would kick in.

    eg.
    Sunday 5:00 am-7:00 pm = 10 reg and 4 OT
    Monday 5:00 am-7:00 pm = 10 reg and 4 OT
    Tuesday 5:00 am-7:00 pm = 10 reg and 4 OT
    Wednesday 5:00 am-7:00 pm = 10 reg and 4 OT
    Thursday 5:00 am-7:00 pm = 4 reg and 10 OT
    Friday 5:00 am-7:00 pm = 14 OT
    Saturday 5:00 am-7:00 pm = 14 OT

    But if we didnt do a full 10 on one of those earlier shifts this is how it would look:
    eg.
    Sunday 5:00 am-7:00 pm = 10 reg and 4 OT
    Monday 5:00 am-12:00 pm = 7 reg and 0 OT
    Tuesday 5:00 am-7:00 pm = 10 reg and 4 OT
    Wednesday 5:00 am-7:00 pm = 10 reg and 4 OT
    Thursday 5:00 am-7:00 pm = 7 reg and 7 OT
    Friday 5:00 am-7:00 pm = 14 OT
    Saturday 5:00 am-7:00 pm = 14 OT

    So before we just hit the straight overtime hours on day 6 and 7 we must complete the 44 regular hours.

    Thank you for your time and the formula so far

  8. #8
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi chichirod,
    sorry! I was not looking throughly, I have changes the previous post file. And also working current requirement. plz check.
    Attached Files Attached Files

  9. #9
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by chichirod View Post
    Could I ask for another revision to this if possible. We do a lot of 15-16 hour days. But the way it works is the first 10 hours of your first 4 shifts are regular time. Anything over that 10 hours is overtime. On the fifth shift the first 4 hours is regular time then everything after that is Overtime. On the 6 and 7 shift everything is straight overtime. However if at some point in the first few shift we dont do our 10 hours of reg time then it pushs everything else back so essential we would have to make it up on the fifth shift where the 4 hours of regular time are before the overtime would kick in.

    eg.
    Sunday 5:00 am-7:00 pm = 10 reg and 4 OT
    Monday 5:00 am-7:00 pm = 10 reg and 4 OT
    Tuesday 5:00 am-7:00 pm = 10 reg and 4 OT
    Wednesday 5:00 am-7:00 pm = 10 reg and 4 OT
    Thursday 5:00 am-7:00 pm = 4 reg and 10 OT
    Friday 5:00 am-7:00 pm = 14 OT
    Saturday 5:00 am-7:00 pm = 14 OT

    But if we didnt do a full 10 on one of those earlier shifts this is how it would look:
    eg.
    Sunday 5:00 am-7:00 pm = 10 reg and 4 OT
    Monday 5:00 am-12:00 pm = 7 reg and 0 OT
    Tuesday 5:00 am-7:00 pm = 10 reg and 4 OT
    Wednesday 5:00 am-7:00 pm = 10 reg and 4 OT
    Thursday 5:00 am-7:00 pm = 7 reg and 7 OT
    Friday 5:00 am-7:00 pm = 14 OT
    Saturday 5:00 am-7:00 pm = 14 OT

    So before we just hit the straight overtime hours on day 6 and 7 we must complete the 44 regular hours.

    Thank you for your time and the formula so far
    The regular hours for Sun stays as is, the overtime hours for all stays as is, but change the Mon formula to

    =IF(OR($A3="FRI",$A3="SAT"),IF(SUM($D$2:$D2)>=44,0,MIN(44-SUM($D$2:$D2),($C3-$B3)*24)),IF($A3="THU",MIN(($C3-$B3)*24,4),MIN(($C3-$B3)*24,10)))

    and copy that down for Tue-Sat

  10. #10
    Hi Sambit,
    It seems really close but for eg. if I work less than 10 reg hours on any of those first shifts than I need to make up the difference on the fifth until I hit 44 reg hours before I can start the straight overtime hour shifts. Would it also be possible for it not read false in the overtime column on days where I do less than 10 hours. Just have it read "0"

Page 1 of 4 1 2 3 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •