+ Reply to Thread
Results 1 to 5 of 5

Comparing Time Values when Formatted as Text (10:00.00 vs 9:00.00)

  1. #1
    Registered User
    Join Date
    12-09-2015
    Location
    Virginia, USA
    MS-Off Ver
    2007
    Posts
    93

    Comparing Time Values when Formatted as Text (10:00.00 vs 9:00.00)

    Hello,

    I am having issues with comparing time values (stored as text) when the numbers being compared are less than 10 and greater than 10. For example, 10 minutes (10:00.00) is registering as less time than 9 minutes (9:00.00). Is there an easy way around this so the comparison is correct. I've got a large list of times, so I have to keep it stored as text to ensure the data remains unaltered by excel and I also concatenate these times in formulas elsewhere in my workbook so I can't do any custom formating.


    A1: 10:10.25
    B1: 9:59.02

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


    It works properly when formatted as General or Custom (m:ss.01), but the problem is when I import the data through my macro, columns A and B do not just contain times (other data includes stuff like 24.25, 11.24cm, "-").

    Thanks in advance for any help!

    RJ

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,569

    Re: Comparing Time Values when Formatted as Text (10:00.00 vs 9:00.00)

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

    Or maybe
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by ChemistB; 02-11-2021 at 10:07 PM.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,449

    Re: Comparing Time Values when Formatted as Text (10:00.00 vs 9:00.00)

    Hi rjj920,

    I think ChemistB is correct. You need to slap a zero on the left of those single digit minutes. Then they would alpha sort correctly. You are trying to do a "<" (less than) using alpha characters. This is a very old problem for Excel.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    12-09-2015
    Location
    Virginia, USA
    MS-Off Ver
    2007
    Posts
    93

    Re: Comparing Time Values when Formatted as Text (10:00.00 vs 9:00.00)

    Thank you guys for the feedback. I may have over-simplified my issue since the formula that I am using is very long and the proposed solution didn't quite work for my application. Would you mind taking a look and see if you can figure out what I am doing wrong. I found another thread which suggested subtracting the two values and testing for whether or not it is greater than 0. This seems to be the best solution for me, but it is giving me other problems (getting value errors).
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,285

    Re: Comparing Time Values when Formatted as Text (10:00.00 vs 9:00.00)

    You might add some helper columns on the DCMaster sheet.
    Borrowing from ChemistB's suggestion try pasting the following into cell P6 then copy over to S6 and then down to S20:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula on Sheet3 could then reference Columns P:S as opposed to I:L
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Excel convert Text time formatted 10h 58m to time so I can sum together
    By slinka in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-04-2016, 10:17 AM
  2. Convert formatted text to fixed text value (time)
    By steez in forum Excel General
    Replies: 2
    Last Post: 04-10-2015, 03:12 PM
  3. [SOLVED] Adding Positive and negative time values formatted as text
    By nixxrite in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 11-22-2014, 02:28 PM
  4. [SOLVED] Comparing numbers in columns that are formatted in time hh:mm:ss
    By FLHRguy in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-01-2012, 09:50 AM
  5. VBA Convesion of Decimal Time Values to Formatted Text
    By Jenn68 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2012, 06:52 PM
  6. Add values to time formatted cells
    By Chutney in forum Excel General
    Replies: 1
    Last Post: 11-30-2009, 07:52 AM
  7. Mod function with time formatted values
    By digita in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-23-2008, 06:59 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