+ Reply to Thread
Results 1 to 5 of 5

Difference between two times - avoiding ###### for times that span midnight

  1. #1
    Registered User
    Join Date
    07-20-2012
    Location
    Buffalo
    MS-Off Ver
    Excel 2007
    Posts
    16

    Difference between two times - avoiding ###### for times that span midnight

    I'm trying to find the difference between two times in (h):mm:ss format using the most basic of functions (B1-A1) but this creates a problem when the times span midnight as it wants to return a negative number. Any better ideas on how to get around this? The date of the times is not available in the data i have.

    Example:

    19:32:46 19:34:09 0:01:23
    23:46:04 0:00:55 #####
    11:07:41 11:57:27 0:49:46
    14:24:45 15:32:11 1:07:26
    17:03:25 17:03:30 0:00:05
    21:29:07 22:18:27 0:49:20
    23:48:20 0:30:26 #####
    15:29:43 16:43:06 1:13:23
    20:11:12 20:41:22 0:30:10
    4:50:04 5:34:40 0:44:36
    17:46:18 18:15:33 0:29:15
    23:20:19 0:36:17 #####
    12:36:26 13:36:18 0:59:52

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: Difference between two times - avoiding ###### for times that span midnight

    Try

    =IF(B1<A1,1-ABS(B1-A1),B1-A1)
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Difference between two times - avoiding ###### for times that span midnight

    try this (by SHG)
    =MOD(60*(DOLLARDE(B1/100,60)-DOLLARDE(A1/100,60)),1440)

    Row\Col
    A
    B
    C
    1
    19:32:46
    19:34:09
    0:01:23
    2
    23:46:04
    0:00:55
    0:14:51
    3
    11:07:41
    11:57:27
    0:49:46
    4
    14:24:45
    15:32:11
    1:07:26
    5
    17:03:25
    17:03:30
    0:00:05
    6
    21:29:07
    22:18:27
    0:49:20
    7
    23:48:20
    0:30:26
    0:42:06
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Registered User
    Join Date
    07-20-2012
    Location
    Buffalo
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Difference between two times - avoiding ###### for times that span midnight

    That worked perfectly! Thank you!

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Difference between two times - avoiding ###### for times that span midnight

    This formula should be sufficient

    =MOD(B1-A1,1)
    Audere est facere

+ 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. If a search term can be found 1 time, 2 times 3 times 4 times 5 times
    By excelcandy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2013, 09:57 PM
  2. getting the difference bewtween two times accounting for midnight cross over
    By superchew in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-12-2013, 11:30 PM
  3. difference between times not working for after midnight.
    By superchew in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-28-2013, 01:47 AM
  4. 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
  5. Replies: 1
    Last Post: 03-08-2005, 11:39 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1