+ Reply to Thread
Results 1 to 39 of 39

How to calculate double time hours between 12 am and 6 am

  1. #1
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    How to calculate double time hours between 12 am and 6 am

    hi guys,


    this is my cell (O11) to calculate straight time hours. Cell N11 is the total number of hours worked. The cells they log in and out are f11 time in, G11 : time out, H11 : time in, I11 : time out, J11 : time in, K11 : time out, L11 : Time in, M11: time out = :=SUM(IF(G11<F11,G11+1,G11)-F11,IF(I11<H11,I11+1,I11)-H11,IF(K11<J11,K11+1,K11)-J11,IF(M11<L11,M11+1,M11)-L11)*24 which calculates total hours in cell N11.
    If they work between the hours of 12 am and 6 am, how to i make :

    Straight time hours =IF(N11<>0,IF(MIN(SUM(N11),8)<5,5,MIN(SUM(N11),8)),0)
    Minus those hours between 12 am and 6 am

    and add them to cell (q11)=IF((N11-O11-P11)<0,0,(N11-O11-P11)) which is double time hours worked

    thank you for any help you can give me.
    Hope this isn't too hard to follow.
    Dann
    Attached Files Attached Files
    Last edited by dwhite11; 08-17-2021 at 02:17 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to calculate double time hours between 12 am and 6 am

    It would be easier for us to work with a sample Excel workbook. The yellow banner at the top of the screen explains how you can attach one to one of your posts.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    Pete_UK,

    I added an attachment the hours are on tab 1. I need straight time to calculate the hours worked at straight time and minus the double time hours worked if they worked hours between 12 am and 6 am. Also add those to the double time.
    Hope you can help and thank you very much

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: How to calculate double time hours between 12 am and 6 am

    Please add sample data andexpected results.

  5. #5
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    Pete-Uk,

    I added with data.
    Winkman should be 6 straight time, 4 ot and 5 DT
    Dinkle should be 2 ST and 3 DT

    Straight time is first 8 hour of the day unless between hours of 12 am and 6 am, then double time with the remaining up to first 8 at ST. also Ot after 8 hours and Double time after 12 hours.
    Thank you for your help
    Dann

  6. #6
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    John,


    I added with data.
    Winkman should be 6 straight time, 4 ot and 5 DT
    Dinkle should be 2 ST and 3 DT

    Straight time is first 8 hour of the day unless between hours of 12 am and 6 am, then double time with the remaining up to first 8 at ST. also Ot after 8 hours and Double time after 12 hours.
    Thank you for your help
    Dann

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to calculate double time hours between 12 am and 6 am

    I don't understand why Winkman would have 6 hours ST and 5 hours DT based on the comment: "8 at ST. also Ot after 8 hours and Double time after 12 hours."
    My proposal employs a helper row per person. The helper rows may be moved and/or hidden for aesthetic purposes.
    The helper rows are populated using: =IF(F9="","",SUM(IF(F9<=6/24,MIN(6/24,G9)-F9,0),IF(G9<6/24,G9-IF(G9<F9,0,F9),0)))
    The formula for ST is: =MIN(8,N9-SUM(F24:L24)*24)
    The formula for OT is: =MIN(4,N9-O9-SUM(F24:L24)*24)
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    JeteMc,
    Winkman has 6 hours because between the hours of 12 am and 6 pm it is double time. So the first 2 before 8am are DT then the next 6 are ST until he reaches his 8 hours at which the next 4 hours are OT. After 12 hours in a day are back to double time. See why I am confused lol.
    Hope you can help and thank you.
    Dann

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: How to calculate double time hours between 12 am and 6 am

    Seem like your ST is from 8AM to 5PM? if yes:

    6am-10am: 2DT + 2ST
    11am-3pm: 4ST
    5pm-10pm= 4OT + 1DT (not 5OT, because maximum of OT is 4)
    11PM-1AM: 2DT
    Total: 6ST + 4OT + 5DT

    Is it correct?
    Quang PT

  10. #10
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53
    Quote Originally Posted by bebo021999 View Post
    Seem like your ST is from 8AM to 5PM? if yes:

    6am-10am: 2DT + 2ST
    11am-3pm: 4ST
    5pm-10pm= 4OT + 1DT (not 5OT, because maximum of OT is 4)
    11PM-1AM: 2DT
    Total: 6ST + 4OT + 5DT

    Is it correct?
    That looks correct.

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to calculate double time hours between 12 am and 6 am

    It appears that double time is paid for time worked between midnight and 8 AM.
    This proposal utilizes five helper rows per person, which should not be an issue as a spread sheet has over one million rows.
    The first helper row calculates the total time worked using: =IF(F$8="in",SUM(G9+(G9<F9)-F9,D17),0)
    The second calculates the ST using: =IF(F$8="in",IF(F9="",0,IF(F17<=8/24,G9+(G9<F9)-F9-F33,0)),0)
    The third calculates DT based on time of day using: =IF(F$8="in",IF(F9="","",SUM(IF(F9<=8/24,MIN(8/24,G9)-F9,0),IF(G9<8/24,G9-IF(G9<F9,0,F9),0))),0)
    The fourth calculates OT using: =IF(AND(SUM($E41:E41)<4/24,F$8="in"),IF(F9="",0,MIN(4/24,G9+(G9<F9)-F9-F25-F33)),0)
    The fifth calculates DT based on over 12 hours worked using: =IF(F$8="in",IF(F9="",0,G9+(G9<F9)-F9-F25-F33-F41),0)
    The ST column is populated using: =SUM(F25:L25)*24
    The OT column is populated using: =SUM(F41:L41)*24
    The DT column is populated using: =SUM(F33:L33,F49:L49)*24
    Let us know if you have any questions.

  12. #12
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    JetMc,
    First of all thank you for taking the time.
    You are On the right track but not quite right. I put in 6am-10 am then 11 am-4 pm and it only gives 2 straight time hours. Double time is hours from 12 am to 6 am but also after 12 hours in one day. so far that looks good. Straight time is the first 8 hours of the day unles it is in those double time hours. so 6 am -10 am would be 2 double and 2 ST. 11 am-4pm would then be 6 ST and 1 OT for a total of 2 DT and 1 OT. Your formulas are giving me 2 ST, 4 OT and 3 DT.
    I really appreciate you working through this with me. I've been struggling with this for some time.

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to calculate double time hours between 12 am and 6 am

    Double time is hours from 12 am to 6 am...so 6 am -10 am would be 2 double and 2 ST.
    This is where I am getting confused.
    It seems to me that based on 12 am - 6 am being the DT hours, 6 am - 10 am and then 11 am - 4 pm should be 8 ST and 1 OT.

  14. #14
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    it is 12 am-8 am, that was a typo, but Double time is hours from 12 am to 6 am but also after 12 hours in one day. so far that looks good. Straight time is the first 8 hours of the day unless it is in those double time hours. so 6 am -10 am would be 2 double and 2 ST and11 am-4pm would then be 6 ST and 1 OT for a total of 2 DT and 1 OT. Your formulas are giving me 2 ST, 4 OT and 3 DT. still holds true.
    The other issue is that I have 100 rows of employees on each page. That is going to make the helper cells a bit cumbersome.
    Again, I appreciate your time and help so thank you

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to calculate double time hours between 12 am and 6 am

    Modifying the ST formula as follows produces 6 ST, 1 OT and 2 DT hours:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I can not see a way to do this without using helper columns, however there are a lot of smart people here, so perhaps someone can.
    Let us know if you have any questions.

  16. #16
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    JeteMC,

    With the latest code you made for me for ST, =IF(F$8="in",IF(F9="",0,G9+(G9<F9)-F9-IF(D17>=8/24,0,MAX(0,F17-8/24))-F33),0)
    it gives me 0.2 ST for the hours 6 am -10 am and11 am-4pm . Thanks again. I appreciate you. It is hard to get anyone to respond to my inquiry. Maybe I don't articulate well
    Dann

  17. #17
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to calculate double time hours between 12 am and 6 am

    In the file attached to post #15 row 9 has the hours 6 am -10 am and 11 am - 4 pm and displays 6 for ST.

  18. #18
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    JeteMc,

    Thank you very much for everything

  19. #19
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to calculate double time hours between 12 am and 6 am

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

  20. #20
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    JeteMc,

    I have an issue if the employee starts in the am say at 3am or 12 am. at 3 ami get 13.0 total hours 1.0 St 4.0 OT 8.0 DT
    I attached the workbook but it is the same one you sent
    Thanks again JeteMc. Sorry to keep bothering you.

  21. #21
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to calculate double time hours between 12 am and 6 am

    I believe that changing the first formula for DT will help.
    Paste the following formula into cell F33, drag the fill handle over to cell L33 and then, while F33:L33 are still selected, drag the fill handle down to cell L39:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  22. #22
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    JeteMC,

    You're the best.
    That seems to work good. You are very smart and thank you so much. One thing I noticed is I can't get rid of the zero in the OT. Displays as 0.0 if i put in 1 am to 4am, 5 am to 10 am. I have cells det to not display zeros in options and even if i write =IF(SUM(F41:L41)*24="",0,SUM(F41:L41)*24) it till displays 0.0. Not a deal breaker, just curious why. Again thank you so much for your help

  23. #23
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to calculate double time hours between 12 am and 6 am

    Using those times SUM(F41:L41)*24 evaluates to 1.33226762955019E-17 (.0000000000000000133226762955019) which is very close to zero but not exactly zero.
    Try the following as the formula for cells P9 and down: =ROUND(SUM(F41:L41)*24,0)
    Let us know if you have any questions.

  24. #24
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    That works for the 0.0 but isnt it going to round the half hour times to whole times?

  25. #25
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to calculate double time hours between 12 am and 6 am

    Good point.
    Try this which should round to the nearest minute: =MROUND(SUM(F41:L41)*24,1/1440)
    Let us know if you have any questions.

  26. #26
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    JeteMc

    that is still rounding the .5 hour into a full hour if i have a half hour time in there

  27. #27
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to calculate double time hours between 12 am and 6 am

    When I put in 6:00 to 10:00 and 11:00 to 16:30 I get ST 6.0 OT 1.5 and DT 2.0 which seems correct to me.
    Please either give us a scenario, or upload a sample that produces the issue.

  28. #28
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    JETEMC,
    That seems to work, Thankl you.
    Last thing, If I want to add a list where the End time for DT is 8 am or 7 am, How difficult is this. I made the list and reformulated the 8/24 to DTS/24 which is the cell. On My actual sheets, at 7am I just get N/A everywhere. Sorry, I am decent at formulas but when time is involved, I just kind of get lost with the decimals.
    Thank you again for everything. I have re-posted the current sheet we are using with the example
    DANN

  29. #29
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to calculate double time hours between 12 am and 6 am

    I am not getting N/A's however I did see that the formula for ST needed changing: =IF(AND(F$8="in",F9<>""),MAX(0,MIN(8/24,G9+(G9<F9)-F9)-D17-F33),0)
    In this formula 8/24 is the maximum number of ST hours (8) so it is not dependent on the end time for DT.
    Let us know if you have any questions.

  30. #30
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    JeteMC,

    If you change the time in the yellow here to 7am which I did here, it gives me the #N/A
    DANN

  31. #31
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    sorry here

  32. #32
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to calculate double time hours between 12 am and 6 am

    Paste the following into cell F33 and then copy over and down to cell L39:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  33. #33
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    Dear JeteMc,

    This looks mostly good. Thank you for being so patient and helping. Look at OT on this sheet. It is weird with these times. Again, I apologize for not being more help. Converting the time to decimals confuses me greatly. I feel like I'm imparting a monumental task upon you. Please let me know if I ask too much.

  34. #34
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    Also if I put in 8 am :1 PM, 2:pm : 7 PM, I get 5 ST 4 OT, 1 DT.
    Should be 8 ST, 2 OT

  35. #35
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to calculate double time hours between 12 am and 6 am

    Paste the following into cell F25 and then copy over and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Paste the following into cell F41 and then copy over and down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  36. #36
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    JeteMc,
    This looks good. I am going to leave this open for a little bit in case something comes up. Thank you so much for your help. You are awesome and thanks for your patience. Is there a way to add you as a friend on here?
    Dann

  37. #37
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to calculate double time hours between 12 am and 6 am

    Thank you for the feedback. I hope that you have a blessed day.

  38. #38
    Registered User
    Join Date
    12-06-2019
    Location
    california
    MS-Off Ver
    office 365
    Posts
    53

    Re: How to calculate double time hours between 12 am and 6 am

    JeteMc, I noticed an issue with OT.
    When I put in 8am-1pm,2pm-6pm,7pm-11pm it gives me 5 hours of Ot when the max should be 4

  39. #39
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: How to calculate double time hours between 12 am and 6 am

    Try pasting the following into cell F41 and then copying over and down to cell L47:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

+ 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. [SOLVED] Timesheet that calcs time in, time out, lunch used, reg hours, ot hours, double hours
    By noobface in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-21-2021, 03:45 AM
  2. Formula for Timesheet - Double Time, Time Half and normal Hours
    By Tracs13 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-12-2017, 09:40 PM
  3. Calculate Overtime and Double time Hours
    By spg62798 in forum Excel General
    Replies: 6
    Last Post: 08-05-2014, 08:20 PM
  4. IF function to work out Ordinary hours, Time 1/2 and Double time!!
    By Jazzy2009 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-13-2014, 11:32 PM
  5. [SOLVED] Formula to Calculate Normal Hours, Time & 1/2 & Double Time from Daily Hours per week
    By KazzICC in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-17-2014, 01:51 AM
  6. Calculating Time and a Half and Double Time (After 40 hours)
    By DoreenBassett in forum Excel General
    Replies: 4
    Last Post: 02-23-2009, 08:10 AM
  7. Calculating Time and Half and Double Time (After 40 hours worked)
    By DoreenBassett in forum Word Formatting & General
    Replies: 1
    Last Post: 02-20-2009, 10:01 AM

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