+ Reply to Thread
Results 1 to 11 of 11

Calculating times between before midnight and after midnight

  1. 10-15-2021, 11:00 AM #1
    FvdF
    FvdF is offline
    Forum Contributor
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    144

    Calculating times between before midnight and after midnight

    Best forumreaders,

    I have this sheet and would like column "G" to be adjusted so that the hours before 24:00 and after 24:00 are also calculated correctly.
    The current formula in this sheet does not work (see row 7).

    This "Copy of Hours on Call.xlsx" file is a copy of a google sheet file. So it should work in a google spreadsheet.

    Column "G" formular is now =IF(F5-D5>0;F5-D5;"") doesn't work.
    This formular is from an Excel sheet an works fine =IF(C5<>"";(((C5>D5)+D5-C5)-E5)*24;"")

    Column "D" and "F" are at final hidden columns.

    With Regards,

    FvdF
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by FvdF; 10-15-2021 at 11:08 AM.
    Reply With QuoteRegister To Reply
  2. 10-15-2021, 11:13 AM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Calculating times between before midnight and after midnight

    Perhaps try
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    pulled down.
    Don't know about GSheets, though
    Reply With QuoteRegister To Reply
  3. 10-15-2021, 05:32 PM #3
    Flyboy65
    Flyboy65 is offline
    Forum Contributor
    Join Date
    12-17-2013
    Location
    ON, Canada
    MS-Off Ver
    MS 365
    Posts
    171

    Re: Calculating times between before midnight and after midnight

    Hi FvdF,

    This might not be the exact layout you want but it's another option for calculating times that go past midnight.

    https://docs.google.com/spreadsheets...it?usp=sharing
    Reply With QuoteRegister To Reply
  4. 10-16-2021, 03:55 AM #4
    FvdF
    FvdF is offline
    Forum Contributor
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    144

    Re: Calculating times between before midnight and after midnight

    Unfortunately, both options do not work for me. Who knows the right solution to this?
    Reply With QuoteRegister To Reply
  5. 10-16-2021, 04:07 AM #5
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,647

    Re: Calculating times between before midnight and after midnight

    Do not work in what way?

    Provide a sample workbook showing what you are trying to achieve.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  6. 10-16-2021, 04:20 AM #6
    FvdF
    FvdF is offline
    Forum Contributor
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    144

    Re: Calculating times between before midnight and after midnight

    Dear Ali,

    See above my first post #1

    I put an atachement file in this post. Hope this is enough for you otherwise i wil see you message.

    Regards,
    FvdF
    Reply With QuoteRegister To Reply
  7. 10-16-2021, 04:25 AM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,647

    Re: Calculating times between before midnight and after midnight

    No - not enough, sorry. Please answer my question and provide an updated copy showing where you have tried the suggestions and indicating what you think is wrong with them.

    Is this an Excel or Google Sheets query???
    Reply With QuoteRegister To Reply
  8. 10-16-2021, 05:14 AM #8
    FvdF
    FvdF is offline
    Forum Contributor
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    144

    Re: Calculating times between before midnight and after midnight

    Dear Ali,

    It is an Google Sheets query. I made a copy from my Google sheet and this is an Excel sheet now, but I need the Google solution.

    I have now reached the point where column "G" seems to be working. Only the total in "G14" is not right now.
    And I cannot get it to work that the times in column "C" and "E" are written with four digits (0800 and 0500).

    Regards.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 10-16-2021, 05:18 AM #9
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Calculating times between before midnight and after midnight

    Works perfectly for me
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 10-16-2021, 05:30 AM #10
    FvdF
    FvdF is offline
    Forum Contributor
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    144

    Re: Calculating times between before midnight and after midnight

    Hello Pepe,

    Yes you are right. I made a mistake with the translation.

    Now we need the 0 for the times 8:00 AM or 5:00 PM in columns "C" and "E" and everything should work for me.
    Reply With QuoteRegister To Reply
  11. 10-16-2021, 05:47 AM #11
    FvdF
    FvdF is offline
    Forum Contributor
    Join Date
    03-22-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016-2019
    Posts
    144

    Re: Calculating times between before midnight and after midnight

    Dear members,

    I think everything now works the way I want it to.

    Thank you all for your help with this.

    Kind regards,
    FvdF
    Attached Images Attached Images
    Attached Files Attached Files
    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] 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
  2. How Can I Work Out a time Before Midnight and After Midnight???
    By ArmandDataCube in forum Excel General
    Replies: 7
    Last Post: 08-23-2017, 06:13 PM
  3. Calculating the total between two times that go over midnight
    By groovybluedog in forum Excel General
    Replies: 11
    Last Post: 07-14-2016, 04:43 PM
  4. [SOLVED] Difference between two times - avoiding ###### for times that span midnight
    By Abarency in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-07-2015, 01:15 PM
  5. Median of Two Times Over Midnight
    By melissaallen5 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-11-2012, 12:56 AM
  6. Calculating hh:mm between two times that span midnight
    By Ymir20000000 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-27-2011, 09:55 AM
  7. Times and midnight issues
    By bslob in forum Excel General
    Replies: 1
    Last Post: 12-08-2007, 01:36 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1