+ Reply to Thread
Results 1 to 5 of 5

Time Card Hour Calculations only for Reg time, Overtime & Doubletime with Lunch Break

  1. #1
    Registered User
    Join Date
    02-14-2018
    Location
    california
    MS-Off Ver
    10
    Posts
    3

    Question Time Card Hour Calculations only for Reg time, Overtime & Doubletime with Lunch Break

    I think I have finally figured out how to get the calculations done for my time sheet into 3 categories, Regular Hours, OT Hours & Double Time Hours.
    I'm running into a problem with the OT hours. I need it to show only a Maximum of 4 hours the remainder go into Double Time.

    here is how my sheet looks with in/out times
    TimeSheetPic.PNG

    Here are my formulas for each:
    Reg Time
    =IFERROR(IF((((C15-B15)+(E15-D15))*24)>8,8,((C15-B15)+(E15-D15))*24), "")
    OT ( I need this to max out at 4 hours)
    =IFERROR(IF((((C15-B15)+(E15-D15))*24)>8,((C15-B15)+(E15-D15))*24-8,0), "")
    Double Time
    =IFERROR(IF((((C15-B15)+(E15-D15))*24)>12,((C15-B15)+(E15-D15))*24-12,0), "")

    Any help is appreciated!
    Last edited by NiecieD; 02-14-2018 at 03:11 PM.

  2. #2
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Time Card Hour Calculations only for Reg time, Overtime & Doubletime with Lunch Break

    Hi, NiecieD!

    Try this formulas:
    Reg Time [F15] : =MIN(8,24*(C15-B15+E15-D15))
    OT [G15] : =MIN(4,24*(C15-B15+E15-D15)-F15)
    Double Time [H15] : =MAX(,24*(C15-B15+E15-D15)-F15-G15)

    Blessings!

  3. #3
    Registered User
    Join Date
    02-14-2018
    Location
    california
    MS-Off Ver
    10
    Posts
    3

    Re: Time Card Hour Calculations only for Reg time, Overtime & Doubletime with Lunch Break

    OT column is only calculating 2 hours and 0 Hours for DT when the final out time is 11p or 10p when using that formula.

    If it matters the cells for in/out are set to number w/2decimal places since there can be quarter hour uses.
    Last edited by NiecieD; 02-14-2018 at 03:42 PM.

  4. #4
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Time Card Hour Calculations only for Reg time, Overtime & Doubletime with Lunch Break

    What is your expected results? Check file with formulas of post # 2 applied. Blessings!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-14-2018
    Location
    california
    MS-Off Ver
    10
    Posts
    3

    Re: Time Card Hour Calculations only for Reg time, Overtime & Doubletime with Lunch Break

    Quote Originally Posted by johnmpl View Post
    What is your expected results? Check file with formulas of post # 2 applied. Blessings!
    OMG I'm so dumb right now! I did not make sure the line was correct. I had applied it to line 19 and it was looking at 15 (as I just did a copy paste)! this works great thank you jihnmpl!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 04-25-2020, 03:23 AM
  2. [SOLVED] Excel Formula to Calculate Time Card with OverTime and DoubleTime
    By LadyThomas in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-14-2017, 09:17 AM
  3. Replies: 8
    Last Post: 11-02-2017, 03:53 PM
  4. Replies: 5
    Last Post: 07-29-2016, 03:15 AM
  5. Can't pull someone's lunch and break at a given time
    By Shellybelly in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-23-2013, 09:01 AM
  6. time calculation without lunch hour
    By Harrold in forum Excel General
    Replies: 7
    Last Post: 01-29-2012, 02:49 AM
  7. reg time, overtime doubletime calculations
    By susanjb in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-04-2005, 12:37 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1