+ Reply to Thread
Results 1 to 13 of 13

Calculating total time worked between a range over midnight dates not involved

  1. 03-13-2019, 03:46 AM #1
    rastip
    rastip is offline
    Registered User
    Join Date
    03-12-2019
    Location
    Slovakia
    MS-Off Ver
    MS Office 2013
    Posts
    9

    Calculating total time worked between a range over midnight dates not involved

    Hi,

    I am trying to calculate the night bonus time which is paid from 22:00 till 6:00, but the shift starts and ends are variable. Say one day it starts from 20:30 till 4:00, but other days 20:00 till 7:00.

    I omitted the date in the calculations to make it simpler.

    I tried daddylonglegs formula : =MAX(0;MIN(D4;"6:00")-MAX("22:00";C4))*24

    also DGagnon formula: =ABS((D4-C4)-IF("22:00">C4;"22:00"-C4;0)-IF(D4>"6:00";D4-"6:00";0))*24

    tied also =((INT(D4)-INT(C4))*("22:00"-"6:00")+MEDIAN(MOD(D4;1);"6:00";"22:00")-MEDIAN(MOD(C4;1);"6:00";"22:00"))*24

    Nothing is working

    I am a newbie, so please be patient with me.

    Thanks for help

    Rusty
    Attached Files Attached Files
    Last edited by rastip; 03-13-2019 at 03:58 AM.
    Reply With QuoteRegister To Reply
  2. 03-13-2019, 04:33 AM #2
    XLent
    XLent is offline
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Calculating total time worked between a range over midnight dates not involved

    so, based on your file:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 03-13-2019, 04:51 AM #3
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Calculating total time worked between a range over midnight dates not involved

    Try this
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 03-13-2019, 05:12 AM #4
    rastip
    rastip is offline
    Registered User
    Join Date
    03-12-2019
    Location
    Slovakia
    MS-Off Ver
    MS Office 2013
    Posts
    9

    Re: Calculating total time worked between a range over midnight dates not involved

    Thank you guys, you stars, both work great
    Reply With QuoteRegister To Reply
  5. 03-13-2019, 11:10 AM #5
    rastip
    rastip is offline
    Registered User
    Join Date
    03-12-2019
    Location
    Slovakia
    MS-Off Ver
    MS Office 2013
    Posts
    9

    Re: Calculating total time worked between a range over midnight dates not involved

    It does not work for calculations which dont go over midnight. For example 10:00 till 18:00, should be blank as it does not cross 22:00
    Last edited by rastip; 03-13-2019 at 11:23 AM.
    Reply With QuoteRegister To Reply
  6. 03-13-2019, 12:37 PM #6
    XLent
    XLent is offline
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Calculating total time worked between a range over midnight dates not involved

    would the below work for you?

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    should cover a lot of scenarios

    Please Login or Register  to view this content.
    etc..
    Reply With QuoteRegister To Reply
  7. 03-14-2019, 03:03 AM #7
    rastip
    rastip is offline
    Registered User
    Join Date
    03-12-2019
    Location
    Slovakia
    MS-Off Ver
    MS Office 2013
    Posts
    9

    Re: Calculating total time worked between a range over midnight dates not involved

    Yes, this works fantastic. Thank you, appreciate your work.
    Reply With QuoteRegister To Reply
  8. 03-14-2019, 04:31 AM #8
    XLent
    XLent is offline
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Calculating total time worked between a range over midnight dates not involved

    sorry, I realised, belatedly, that the above still isn't quite right - should you have start / end times that fall within the night shift but on same day, e.g. 03:00-05:00, or 22:30-23:45

    revised version below:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    I can't help but think this could be greatly simplified whilst still accounting for various scenarios laid out in below table...
    {new additions, catered for in above revision, shown in red}

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 03-14-2019, 08:06 AM #9
    rastip
    rastip is offline
    Registered User
    Join Date
    03-12-2019
    Location
    Slovakia
    MS-Off Ver
    MS Office 2013
    Posts
    9

    Re: Calculating total time worked between a range over midnight dates not involved

    Work great, just getting value error on blank cell after I add the 24 time . Can you check where I need to fix this please.

    But great job

    excel.JPG
    Reply With QuoteRegister To Reply
  10. 03-14-2019, 08:10 AM #10
    XLent
    XLent is offline
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Calculating total time worked between a range over midnight dates not involved

    you need to move your *24 inside of the calc for non-blank rows

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 03-14-2019, 09:50 AM #11
    rastip
    rastip is offline
    Registered User
    Join Date
    03-12-2019
    Location
    Slovakia
    MS-Off Ver
    MS Office 2013
    Posts
    9

    Re: Calculating total time worked between a range over midnight dates not involved

    I see it now.

    Thanks, prefect
    Reply With QuoteRegister To Reply
  12. 03-14-2019, 10:06 AM #12
    rastip
    rastip is offline
    Registered User
    Join Date
    03-12-2019
    Location
    Slovakia
    MS-Off Ver
    MS Office 2013
    Posts
    9

    Re: Calculating total time worked between a range over midnight dates not involved

    Just one added question.

    Whats the N function in this formula?

    =IF(N($E3)=0;"";
    Reply With QuoteRegister To Reply
  13. 03-14-2019, 10:09 AM #13
    XLent
    XLent is offline
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Calculating total time worked between a range over midnight dates not involved

    see: https://www.techonthenet.com/excel/formulas/n.php

    you could equally use COUNT or SUM -- it's just to verify that there are net hours (i.e. row requires calculation).
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculating total time worked between a range over midnight.
    By amurr in forum Excel General
    Replies: 10
    Last Post: 03-13-2019, 03:27 AM
  2. Calculating hours worked beyond 12 midnight
    By tyneexi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-01-2018, 07:31 AM
  3. Calculating Hours Worked Past Midnight - Without no time entries equating to 24 hours.
    By mttomkins in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-13-2017, 11:05 AM
  4. Calculating Time Worked when Clocking out after Midnight
    By tstjuste in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-27-2013, 03:05 PM
  5. [SOLVED] Calculating hours worked beyond midnight
    By Boville in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-25-2012, 12:22 PM
  6. calculating total time worked to measure SLA
    By billsoule in forum Excel General
    Replies: 4
    Last Post: 12-01-2010, 05:49 PM
  7. Calculating Total Time Worked on a
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-23-2005, 12:29 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1