+ Reply to Thread
Results 1 to 7 of 7

Time sums after midnight

  1. #1
    Registered User
    Join Date
    06-14-2019
    Location
    Atlanta, GA
    MS-Off Ver
    Office 365
    Posts
    3

    Time sums after midnight

    Hey Everyone,

    I was stopping by because I have tried hundreds of formulas and cannot get anything that occurs after midnight to work correctly. If you can please take a look at my attachment! I am building a calculator that will feed another database how many members we will have in the building base off of multiple variables. Any help you can give will be much appreciated!
    Attached Files Attached Files
    Last edited by Pepe Le Mokko; 06-14-2019 at 09:22 AM. Reason: Removed unnessary title part

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Time sums after midnight

    "cannot get anything that occurs after midnight to work correctly."

    What does this mean? Can you be more specific about what result you want, and what you are getting instead that is wrong? It looks like you are calculating an End Time based on start time and other variables, and you are getting valid results after midnight for some of them. I don't see anything wrong here.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    06-14-2019
    Location
    Atlanta, GA
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Time sums after midnight

    Yes,

    If you look in the enter field between column B and N everything is working fine. This issue occurs in the next section that sums in columns AE:BB. This area is to calculate the amount of hours
    we have by team member type during any given hour while removing times for break, lunches, etc. It works perfectly except when the end time in Column K goes to midnight or later. Once K goes
    midnight or later then everything under that section (by employee type)in AE:BB calculates incorrectly.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Time sums after midnight

    The problem is that you are using time of day for the start and ends times without coupling it with a date. In the example of Coordinator, the end time of 12:40 AM comes before the start time of 4:00 PM, so the arithmetic doesn't work.

    To fix this, you have to calculate the end time so that it is in the following day if it goes past midnight. Change the formula in K5 to this

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


    and copy down the column. Your formula was more complicated than it needed to be so this simplifies it. Also, instead of just using TIME to determine a time, it adds the time increment to the start time so that if it rolls past midnight, it counts an extra day. Your display format just shows the time so this will look the same to the user as what you have now, but your results farther to the right will be correct.

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Time sums after midnight

    Implemented in your file:
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-14-2019
    Location
    Atlanta, GA
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Time sums after midnight

    Quote Originally Posted by 6StringJazzer View Post
    The problem is that you are using time of day for the start and ends times without coupling it with a date. In the example of Coordinator, the end time of 12:40 AM comes before the start time of 4:00 PM, so the arithmetic doesn't work.

    To fix this, you have to calculate the end time so that it is in the following day if it goes past midnight. Change the formula in K5 to this

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


    and copy down the column. Your formula was more complicated than it needed to be so this simplifies it. Also, instead of just using TIME to determine a time, it adds the time increment to the start time so that if it rolls past midnight, it counts an extra day. Your display format just shows the time so this will look the same to the user as what you have now, but your results farther to the right will be correct.


    Awesome that worked perfect! I truly appreciate your assistance.

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Time sums after midnight

    You're welcome and thanks for the rep!

+ 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] 24h time cross midnight; Nested IF not working for an earlier time
    By bren84 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-08-2019, 03:38 PM
  2. [SOLVED] Want to extract time before and after midnight for a time range that spans midnight
    By schurchill39 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-26-2018, 04:18 PM
  3. [SOLVED] Calculating time differences in 24 hour time when going past midnight
    By Thug in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-29-2018, 10:22 AM
  4. 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
  5. how to convert time if it's past midnight in military time
    By Jomejorada in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-15-2015, 07:50 AM
  6. [SOLVED] Text Time to Excel Format, Then Sort Time Past Midnight
    By BuntyMac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-27-2014, 03:13 AM
  7. Replies: 4
    Last Post: 01-11-2012, 07:59 PM

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