+ Reply to Thread
Results 1 to 7 of 7

Counting hours between set times across midnight

  1. #1
    Registered User
    Join Date
    10-29-2023
    Location
    sWEDEN
    MS-Off Ver
    2016
    Posts
    3

    Question Counting hours between set times across midnight

    There is no lack of threads regarding time calculations over crossing over between midnight. That in itself is no issue, I've tried =MOD(C2-B2; 1) and many other ways to calculate this and followed many guides.

    I am creating an automated way to calculate hours passed with different rates depending on WHEN they occur.
    A2: Date
    B2: Start time
    C2: End time
    F2: Daytime during the weekdays between 6AM and 6PM
    G2: Evenings during the weekdays between 6PM and 12PM
    H2: Nights during the weekdays between 12PM and 6AM
    J2: Weekends 12AM to 12PM

    Example for G2:
    =IF(AND(WEEKDAY(A2; 2)<=5; OR(HOUR(B2)<18; HOUR(C2)>18));
    MAX(MIN(HOUR(C2) + MINUTE(C2)/60; 24) - MAX(HOUR(B2) + MINUTE(B2)/60; 18); 0); 0)

    This works, but as soon as the time goes over midnight, for an example:
    6PM Start time
    2AM End time
    G2(hours counted 6PM to 12PM) = 0

    Anyone knows a good way to deal with crossing over midnight? Maybe there is a much easier way to go about this.
    Attached Files Attached Files
    Last edited by edynam; 10-29-2023 at 10:15 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,527

    Re: Counting hours between set times across midnight

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,203

    Re: Counting hours between set times across midnight

    Quote Originally Posted by edynam View Post
    ... the time goes over midnight, for an example:
    6pm start time
    2am end time
    b2 -> 18:00:00
    c2 -> 02:00:00
    d2 -> =c2+(1-b2)

  4. #4
    Registered User
    Join Date
    10-29-2023
    Location
    sWEDEN
    MS-Off Ver
    2016
    Posts
    3

    Re: Counting hours between set times across midnight

    Thank you, however, counting the hours over midnight is already solved, but I did it with =MOD(C2-B2; 1)

    The problem rises with counting specific hours in set time spans. E.g. between 6AM and 6PM, between 6PM and 12PM and so on.

    I have attached an example of what I want to achieve in the first post now.

    Thanks again for taking your time,
    Ed

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

    Re: Counting hours between set times across midnight

    Try

    D2
    PHP Code: 
    =MOD(C2+(C2<B2)-B21
    E2
    PHP Code: 
    =SUMPRODUCT((F2:I2*{315,405,450,490})) 
    F2
    PHP Code: 
    =IF(WEEKDAY($A2,2)<6,MIN($C2+($C2=0),18/24)-MAX($B2,6/24),0)*24 
    G2
    PHP Code: 
    =IF(WEEKDAY($A2,2)<6,MIN($C2+($C2=0),1)-MAX($B2,6/24),0)*24-F2 
    H2
    PHP Code: 
    =IF(WEEKDAY($A2,2)<6,IF($C2<$B2,MIN(6/24,$C2), MIN(6/24,$C2)-MIN(6/24,$B2))*24,0
    I2
    PHP Code: 
    =IF(WEEKDAY($A2,2)>5,IF(C2>B2,C2-B2,1-B2),0)*24 
    Attached Files Attached Files
    Quang PT

  6. #6
    Registered User
    Join Date
    10-29-2023
    Location
    sWEDEN
    MS-Off Ver
    2016
    Posts
    3

    Re: Counting hours between set times across midnight

    Thank you so much for taking your time!

    It seems to work in all cases, except when crossing over midnight if the start time B2 is more than 18. Then it returns a MINUS value in F.

    See attachment
    Attached Files Attached Files

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

    Re: Counting hours between set times across midnight

    F2 then drag down:

    Please Login or Register  to view this content.

+ 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] Calculating times between before midnight and after midnight
    By FvdF in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 10
    Last Post: 10-16-2021, 05:47 AM
  2. [SOLVED] Calculate how many hours between two times are before midnight and after midnight
    By annazet in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-05-2019, 09:47 AM
  3. [SOLVED] Formula Needed - Hours between times crossing midnight
    By meowmix7903 in forum Excel - New Users/Basics
    Replies: 10
    Last Post: 08-30-2017, 05:52 AM
  4. Replies: 10
    Last Post: 08-26-2016, 02:01 AM
  5. Counting Only Specific Hours Between Two Times
    By Pondus in forum Excel General
    Replies: 13
    Last Post: 12-20-2011, 07:38 AM
  6. Counting Hours Between Two Times Unless Text
    By grumpyrogue in forum Excel General
    Replies: 4
    Last Post: 12-18-2011, 10:45 PM
  7. counting specific hours between two times
    By each in forum Excel General
    Replies: 4
    Last Post: 04-20-2009, 04:08 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1