+ Reply to Thread
Results 1 to 8 of 8

How Can I Work Out a time Before Midnight and After Midnight???

  1. #1
    Registered User
    Join Date
    08-22-2017
    Location
    South Africa
    MS-Off Ver
    2013
    Posts
    2

    How Can I Work Out a time Before Midnight and After Midnight???

    Hello

    I am trying to get a time Back between Set times.

    Night Start 22:00:00
    Night End 04:00:00
    Start T End T
    23:00:00 05:30:00 ?
    14:00:00 02:00:00 ?

    I only need the Value's between Set Times, I do not need the time diff , i need the time that is left?? If the Employ Start at 17:00 PM and his shift ended at 02:00 AM - I need how many hours between 22:00 pm and 00:00 and the time at work Between 00:00 and 02:00 .


    Help Please?????
    Last edited by ArmandDataCube; 08-23-2017 at 03:37 AM.

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: How Can I Work Out a time Before Midnight and After Midnight???

    Perhaps

    if(a1<b1,b1-a1,1-a1+b1)

    if start is less than finish just take start away from finish
    otherwise 1-start is hours to midnight, so 1-start+finish is the hours worked

  3. #3
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: How Can I Work Out a time Before Midnight and After Midnight???

    Hi all- assuming these are Excel times (which are actually just fractions of a day):
    Hours worked = MOD(End_time - Start_time, 1)

    Format cell as time, or multiply by 24 if you want minutes as fractions of hours.

    Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee
    Last edited by leelnich; 08-22-2017 at 07:01 AM.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How Can I Work Out a time Before Midnight and After Midnight???

    Also another way would be to include dates with the times. This takes advantage of what leelnich referred to. Trans midnight events become a non problem.

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



    A
    B
    C
    D
    1
    Time difference
    2
    Start
    8/22/2017
    22:00
    6:00
    3
    End
    8/23/2017
    4:00



    Or combine date/time in the same cell. Then the formula is simpler.
    Formula: copy to clipboard
    Please Login or Register  to view this content.




    A
    B
    C
    1
    Time difference
    2
    Start
    8/22/2017 22:00
    6:00
    3
    End
    8/23/2017 4:00
    Last edited by FlameRetired; 08-22-2017 at 11:26 AM.
    Dave

  5. #5
    Registered User
    Join Date
    08-22-2017
    Location
    South Africa
    MS-Off Ver
    2013
    Posts
    2

    Re: How Can I Work Out a time Before Midnight and After Midnight???

    Thanks I did used Mod - is not the Value I need

  6. #6
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: How Can I Work Out a time Before Midnight and After Midnight???

    Row\Col
    A
    B
    C
    1
    Night Start
    22:00:00
    2
    Night End
    4:00:00
    3
    4
    Start T End T Result
    5
    23:00:00
    5:30:00
    0.5
    6
    14:00:00
    2:00:00
    6


    C5
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  7. #7
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: How Can I Work Out a time Before Midnight and After Midnight???

    Sorry, I initially misinterpreted the requirements. Perhaps this?:
    ...In C4 and copy down:
    Please Login or Register  to view this content.
    ...In D4 and copy down:
    Please Login or Register  to view this content.
    Row\Col
    A
    B
    C
    D
    1
    Shift start
    shift end
    2
    22:00
    4:00
    3
    Start
    End
    Hrs before midnight Hrs after midnight
    4
    23:00
    1:00
    1:00
    1:00
    5
    1:00
    7:00
    0:00
    3:00
    6
    20:00
    23:00
    1:00
    0:00
    7
    19:00
    22:00
    0:00
    0:00
    8
    4:00
    12:00
    0:00
    0:00
    9
    22:00
    4:00
    2:00
    4:00
    10
    18:00
    20:00
    0:00
    0:00
    11
    22:00
    23:00
    1:00
    0:00
    12
    20:00
    3:00
    2:00
    3:00


    Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How Can I Work Out a time Before Midnight and After Midnight???

    Yup. I misinterpreted too. Sorry.

+ 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. time value after midnight
    By michelindb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-14-2013, 07:23 PM
  2. time value after midnight
    By michelindb in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-12-2013, 03:24 PM
  3. Average Time Across Midnight
    By Banttari in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-18-2013, 11:38 AM
  4. [SOLVED] Time calculations when going over the midnight
    By ABSTRAKTUS in forum Excel General
    Replies: 4
    Last Post: 06-03-2012, 12:20 PM
  5. Time worked after midnight
    By kaps_mr2 in forum Excel General
    Replies: 5
    Last Post: 04-30-2012, 07:45 AM
  6. Calculating work time when end time is after midnight.
    By dpatchie in forum Excel General
    Replies: 1
    Last Post: 02-01-2012, 11:10 AM
  7. Midnight as TIME()
    By ZooTV92 in forum Excel General
    Replies: 8
    Last Post: 07-03-2011, 08:38 AM
  8. [SOLVED] subtraction off time after midnight
    By Steved in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-23-2006, 08:50 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