+ Reply to Thread
Results 1 to 7 of 7

Tiimelog - Time Difference Crossing Over or Past Midnight

  1. 10-18-2016, 11:58 PM #1
    mycon73
    mycon73 is offline
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,054

    Question Tiimelog - Time Difference Crossing Over or Past Midnight

    Hi,

    I created a timelog & have the following:

    Cells doesn't matter....

    Time Start: 11:30 pm
    Time End: 12:30 am.

    Should show: 1:00 or 1 hour

    Instead, getting "####"

    How to avoid this & get my calculated hour duration?

    Thanks
    Last edited by mycon73; 10-19-2016 at 11:41 PM.
    MyCon
    -- Using Latest Version of Excel
    Reply With QuoteRegister To Reply
  2. 10-19-2016, 12:52 AM #2
    mycon73
    mycon73 is offline
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,054

    Re: Tiimelog - Time Difference Crossing Over or Past Midnight

    Hi,

    Does anyone have a solution?
    Reply With QuoteRegister To Reply
  3. 10-19-2016, 02:23 AM #3
    Jamesera27
    Jamesera27 is offline
    Registered User
    Join Date
    04-28-2014
    Location
    Florida
    MS-Off Ver
    Excel 2007, 2010, 2011 Mac. 2013,2016. I use 2011 and 2016 the most
    Posts
    90

    Re: Tiimelog - Time Difference Crossing Over or Past Midnight

    http:///www.computing.net/answers/of...ons/12834.html
    Reply With QuoteRegister To Reply
  4. 10-19-2016, 09:30 AM #4
    Alf
    Alf is offline
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Tiimelog - Time Difference Crossing Over or Past Midnight

    Another way to solve this problem would be with the help of the MOD function.

    Assuming that cell B1 contains start time i.e. 11:30 PM and B2 contains stop time i.e. 12:30 AM a formula like this

    Please Login or Register  to view this content.
    will give a result of 1:00 AM so to to just get the 1:00 use custom format on the target cell and set this to [t]:mm

    Not sure about the [t] as this is the Scandinavian setting, you probably should use [h]. Also the formula delimiter in Scandinavia is ";" but I assume you would use "," in the MOD formula.

    Alf
    Reply With QuoteRegister To Reply
  5. 10-19-2016, 11:16 PM #5
    mycon73
    mycon73 is offline
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,054

    Re: Tiimelog - Time Difference Crossing Over or Past Midnight

    Hi Alf & Others,

    Thanks for replying.

    I tried: =MOD(B2-B1,1)

    Getting: 13:00 instead of 1:00

    I attempted to change the format but it's not excepting the "t".

    What are the other options?
    Reply With QuoteRegister To Reply
  6. 10-19-2016, 11:31 PM #6
    mycon73
    mycon73 is offline
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,054

    Re: Tiimelog - Time Difference Crossing Over or Past Midnight

    Hi Alf,

    =MOD(B2-B1,1) works great!!!

    I had a typo error previously

    Thanks for the assistance.....
    Reply With QuoteRegister To Reply
  7. 10-20-2016, 06:41 AM #7
    Alf
    Alf is offline
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Tiimelog - Time Difference Crossing Over or Past Midnight

    You are welcome.

    Thanks for feedback and rep

    Alf

    Ps What is the proper substitute for [t] (Scandinavian for timme i.e. "hour") is this [h]?
    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] 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
  2. [SOLVED] formula to calculate time difference crossing midnight
    By ditorejax in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-06-2014, 08:27 PM
  3. Time crossing midnight
    By belle08 in forum Excel General
    Replies: 4
    Last Post: 01-27-2014, 03:47 PM
  4. Timesheet: Calculate Time within a period when crossing midnight
    By G0liath02 in forum Excel General
    Replies: 13
    Last Post: 11-04-2013, 02:30 AM
  5. Excel 2007 : Notification time Plus - real time tracking PAST midnight with Conditional formatting
    By theymademeregister in forum Excel General
    Replies: 4
    Last Post: 01-11-2012, 07:59 PM
  6. Calculate time which is past midnight
    By stoey in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 08-25-2011, 12:11 PM
  7. [SOLVED] Calculating Time Past Midnight
    By Darren in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-13-2006, 11:03 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