+ Reply to Thread
Results 1 to 8 of 8

Comparing Time Values

  1. #1
    Forum Contributor
    Join Date
    12-09-2015
    Location
    Virginia, USA
    MS-Off Ver
    2007
    Posts
    121

    Comparing Time Values

    I've had issues with this before and was able to get it for comparing times between 9:00 and 10:00 where it was showing 9:00 being greater than 10:00. Now my problem is 90% solved, but I realized my new formula to account for 9 vs 10 is now messing up 1:00 vs 59 seconds. I've attached a workbook to show my exact problem. The formula is pretty long, but hopefully someone can help!

    The formula is perfect except for the highlighted yellow row.
    Attached Files Attached Files

  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: Comparing Time Values

    I can't make head nor tails out of this file. The long formulas you are using to populate columns F and G are too long for me to reverse engineer. The results of those formulas are not time values, and there is no place where you have a formula that compares F8 to G8, so I don't understand the question.

    The only observation I have is that your raw data on DCMaster is all text. If the time values were actually time values then this whole problem wouldn't exist.

    time.JPG
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    12-09-2015
    Location
    Virginia, USA
    MS-Off Ver
    2007
    Posts
    121

    Re: Comparing Time Values

    Yeah, I had import as text due to these columns containing various types of data (hence the long formulas too). These columns contain times, measurements, text, dates, etc, so the only way to preserve the integrity of the time is via text.

    "Time" means to compare DCMaster I6 and K6. If I6 is less than K6, then add a superscript "a""
    "Measure" means it’s a measurement and to concatenate DCMaster I15 with J15 (it's conversion in Imperial) and K15 with L15. Then if I15 is GREATER than K15, add superscript "a"

    Not sure if that clears up anything or not...
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Comparing Time Values

    tl:dr: We might need more information -- especially about where this data comes from and how you need to interact with the data and why you believe that "the only way to preserve the integrity of the time is via text." If this were my project, I would be looking for ways around the "must store as text" restriction so that I could store and interact with these times as numbers.

    The short but useless answer to the question is that any text string that begins with a "1" character is going to be "smaller" than any text string that begins with a "5" character. IMO, the easiest way to "fix" this is to convert the times to numbers (fraction of a day, if you want to use Excel's built in date/time number formatting), then they can be compared as numbers. If you must interact with these as text strings, then you must do something to these text strings so that the text sort order will match numeric sort order. From what I see, this usually involves prepending something (maybe "0:") when appropriate to the resulting text string or some other manipulation (could you store 1:01.10 as 61.10, for example). As 6stringjazzer indicated, the formulas are a bit unwieldy to try and reverse engineer, so I don't readily see what could be changed in those formulas to modify the output text. I also agree with him that, if we could see past the "must store as text" limitation to find some way to store the times as numbers, we could resolve the issues that arise from storing numbers as text.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Contributor
    Join Date
    12-09-2015
    Location
    Virginia, USA
    MS-Off Ver
    2007
    Posts
    121

    Re: Comparing Time Values

    Yeah, I import a csv file that is 30 columns long. The format is consistent but the data it contains is not, so I have to determine which type of file it is after I import it into excel. I then take this data and paste it into other sheets in excel to generate a report so the constant copying pasting becomes troublesome for me to preserve my formats.

  6. #6
    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: Comparing Time Values

    If I6 is less than K6, then add a superscript "a""[...] Then if I15 is GREATER than K15, add superscript "a"
    At a bare minimum you need to put the superscript in a separate column and put true time values in F and G. Then they can be easily compared.

  7. #7
    Forum Contributor
    Join Date
    12-09-2015
    Location
    Virginia, USA
    MS-Off Ver
    2007
    Posts
    121

    Re: Comparing Time Values

    Thank you guys for your help and feedback. I ended up just using len() formula to dictate which time was greater or less than for step one of my if statement.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,417

    Re: Comparing Time Values

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. Comparing Time Values when Formatted as Text (10:00.00 vs 9:00.00)
    By rjj920 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-17-2021, 02:15 PM
  2. Time - comparing then adding and/or subtracting values from a table
    By iburgess in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-29-2019, 12:33 PM
  3. [SOLVED] Comparing time values in 2 different sheets
    By PrestonT in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-15-2016, 04:59 PM
  4. Comparing two time values in excel
    By Devika Gambhir in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-23-2013, 10:33 AM
  5. Replies: 4
    Last Post: 12-23-2012, 09:10 AM
  6. Error when comparing time values
    By cas8100 in forum Excel General
    Replies: 6
    Last Post: 12-07-2009, 12:41 PM
  7. Comparing a string to values in an array resulting in a Run time Error 1004
    By sgreni in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2009, 04:21 PM

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