+ Reply to Thread
Results 1 to 8 of 8

Excel Formula to Calculate Time Card with OverTime and DoubleTime

  1. #1
    Registered User
    Join Date
    11-10-2017
    Location
    High Desert
    MS-Off Ver
    2013
    Posts
    4

    Unhappy Excel Formula to Calculate Time Card with OverTime and DoubleTime

    Hello,
    I am having an issue with excel. I am new with formulas so I am unable to figure this one out. I do not know much about how to create formula's. My spreadsheet TimeCard needs to calculate Regular Hours, OverTime hours and Doubletime hours worked.

    The issue I am having is, I have been asked to format the spreadsheet to show if an employee worked 13 hours that day, then the Regular Hours Worked (column L) should only show 8 hours, and the remaining overtime hours worked (column M) should show 4 hours, and the Doubletime hours worked (column N) should show 1 hour to equal the 13 hours. (anything over 4 hours of overtime is considered double time.)

    I can only get the spreadsheet to calculate all the hours ----8 hours show in regular hours column L and the remaining 5 hours show in column M. How can I get the spreadsheet to calculate correctly to where any remaining overtime hours over 4 hours in column M automatically roll over to the doubletime column N?

    Any help will be much appreciated. I also hope I explained myself correctly. Thank you for your time. I have attached a spreadsheet
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,658

    Re: Excel Formula to Calculate Time Card with OverTime and DoubleTime

    instead of IF I'd use MIN and MAX functions
    L3:N3 resp. and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    remember to format column L as general or number
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    11-10-2017
    Location
    High Desert
    MS-Off Ver
    2013
    Posts
    4
    Kaper, thank you so much. I will try it and let you know how it works for me.
    Last edited by AliGW; 11-13-2017 at 02:05 AM. Reason: Unnecessary quotation removed.

  4. #4
    Registered User
    Join Date
    11-10-2017
    Location
    High Desert
    MS-Off Ver
    2013
    Posts
    4

    Re: Excel Formula to Calculate Time Card with OverTime and DoubleTime

    Kaper,
    Thank you so much. That worked for me. Thank you. I surely do appreciate your help. I do have one other question.

    How do I get the formula to calculate if they worked into the following day without it showing -hours in the hours column? so person worked 12 pm until 12:30 am the next day.

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,658

    Re: Excel Formula to Calculate Time Card with OverTime and DoubleTime

    The easy way would be to compare two always filled in columns and if end hr is earlier than start hr then it was work in night and we have to add 1 day to a difference (I hope nobody works 24 hrs+ ):

    so if(K3<F3,1;0)
    but to make formulas shorter we could write it just
    +(K3<F3)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,658

    Re: Excel Formula to Calculate Time Card with OverTime and DoubleTime

    see Tatum Jenkins and Erik Ramirez in attached file
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-10-2017
    Location
    High Desert
    MS-Off Ver
    2013
    Posts
    4

    Re: Excel Formula to Calculate Time Card with OverTime and DoubleTime

    KAPER,

    IT WORKED THANK YOU SO MUCH. haha Nobody works more than 24 hours in a day. Its just that sometimes the employees work early evening and carryon til the next day.

    I really appreciate all of your help. Do you by chance tutor for excel or give any suggestions on where I can get tutoring besides going to school to take classes? I can sure use it.

    There are more things that I would like to learn in excel and at this point it wouldn't hurt to get teaching for it.

  8. #8
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,658

    Re: Excel Formula to Calculate Time Card with OverTime and DoubleTime

    Well, for sure not more than 24h/day :-) but I know the case, when someboty is working continously from say Nov 14th 10:00 AM to Nov 15th 2:00 PM (sounds like 28hrs) with 4 breaks (including 9hrs sleep break, and 3 1hr meals breaks) - so in total 16hrs) and then have 1 day off. (sleeping is on-site).

    Anyway as the above seems to take care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Thanks.

+ 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: 3
    Last Post: 01-21-2016, 06:07 PM
  2. Replies: 3
    Last Post: 01-16-2016, 06:37 AM
  3. Help on formula(s) for calculating overtime/doubletime in CA
    By Jason Andi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-31-2014, 02:46 AM
  4. lCalculating Hours, overtime, and doubletime for payroll
    By Delfino909 in forum Excel General
    Replies: 0
    Last Post: 09-07-2012, 08:05 PM
  5. Time Card - Carrying Overtime
    By mr.dale in forum Excel General
    Replies: 3
    Last Post: 03-05-2011, 02:27 PM
  6. Calculate overtime and doubletime
    By shizuka in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 03-06-2009, 05:01 PM
  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