+ Reply to Thread
Results 1 to 10 of 10

Comparing numbers in columns that are formatted in time hh:mm:ss

  1. #1
    Registered User
    Join Date
    10-31-2012
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2003
    Posts
    3

    Comparing numbers in columns that are formatted in time hh:mm:ss

    I have a huge amount of time entries to compare. Column A would be the Original entry and column B will be either a matching entry or a modified entry which can be greater than or less than the Original entry. I was able to get an answer for the formula where the B entry was greater than the original entry by using "SUM", but cannot figure out how to get an answer where the B entry is less than A.

    A B
    2:07:39 PM 2:07:39 PM 0:00:00

    4:31:24 PM 5:30:00 PM 0:58:36

    11:31:16 PM 6:00:00 PM #VALUE!
    Attached Files Attached Files
    Last edited by FLHRguy; 10-31-2012 at 10:31 PM. Reason: add an attachment

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,814

    Re: Comparing numbers in columns that are formatted in time hh:mm:ss

    How are you using SUM?

    Maybe, =IF(A>B, A-B, B-A)

    Regards, TMS
    Last edited by TMS; 11-01-2012 at 01:39 AM.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Comparing numbers in columns that are formatted in time hh:mm:ss

    Hi,

    One way
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Richard Buttrey; 10-31-2012 at 08:02 PM.

  4. #4
    Registered User
    Join Date
    10-31-2012
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Comparing numbers in columns that are formatted in time hh:mm:ss

    Ok, i must be brain dead after working with this all day. Both of your formula's give TRUE as the response but i need the the difference expressed in "time". I have attached a small sample to work with, hoping that is easier. My file is 25,000+ entries.

    Thanks...

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: Comparing numbers in columns that are formatted in time hh:mm:ss

    unless i'm missing it, I dont see you're attachment?

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Contributor
    Join Date
    09-23-2008
    Location
    Mexico
    Posts
    200

    Re: Comparing numbers in columns that are formatted in time hh:mm:ss

    Try:
    =IF(B2<A2,B2+1-A2,B2-A2)
    Cell format: h:mm:ss
    Last edited by Armando Montes; 10-31-2012 at 11:23 PM. Reason: Typo

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,007

    Re: Comparing numbers in columns that are formatted in time hh:mm:ss

    based on the info you provided, try this...
    =IF(B2>A2,B2-A2,A2-B2)

    edit: make sure you format the cell to time (i used the option for 37:30:55)
    Last edited by FDibbins; 10-31-2012 at 11:32 PM.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,814

    Re: Comparing numbers in columns that are formatted in time hh:mm:ss

    Thanks for the rep.

    Just to confirm: =IF(A2>B2,A2-B2,B2-A2) copied down and formatted as Time (whatever you want it to look like)


    Regards, TMS

  9. #9
    Registered User
    Join Date
    10-31-2012
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Comparing numbers in columns that are formatted in time hh:mm:ss

    Yes, this formula seemed to work best with the spreadsheet i am using. Some of the other solutions may have worked as well, but this worked for me.

    This is an amazing help site. I was so impressed with the quick help from around the globe!

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,814

    Re: Comparing numbers in columns that are formatted in time hh:mm:ss

    You're welcome.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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