Comparing time with different formats

[CaptainGravyBum]

Hello,
I think I'm going to answer my own question with this one, but there's probably an easy fix.
On the sheet attached, I need to check if the time in column B is less (earlier) than column A. It isn't working correctly and both columns are formatted as time, but I think column B has the date included in the data and I don't know how to either strip that out or add a date into the information in column A so it can do a correct comparison.
I do have the date relevant to column A in my main sheet, if it's just a case of combining the two columns.

Time.xlsx
	A	B	C
1	12:00:00	12:54:00	TRUE
Sheet1

Cell Formulas
Range	Formula
C1	C1	=B1<A1

 

[etaf]

if you have a date
then in column B

(B1-INT(B1))<a1

date is a number and time is the decimal
so
B1-INT(B1)
removes the number part and just leaves the decimal

 

[CaptainGravyBum]

if you have a date
then in column B

(B1-INT(B1))<a1

date is a number and time is the decimal
so
B1-INT(B1)
removes the number part and just leaves the decimal

Hi @etaf ,
Thanks for the reply, still isn't working, I think now it's because the time in A isn't converted to decimal, if I change the formatting to general A is 12:00:00 and B shows decimal 46501.54.

 

[etaf]

yep its text
try clicking on column A
DATA
Text to columns
and just do next , next etc
se eif that now changes from text to real date -

 

[CaptainGravyBum]

yep its text
try clicking on column A
DATA
Text to columns
and just do next , next etc
se eif that now changes from text to real date -

That works! My only issue is that my original data is fed from other sheets, is there a way to ensure it only returns the correct value from a vlookup?

 

[etaf]

one way is to convert the text to real dates on the other sheets using text to columns
OR
=Timevalue( your vlookup formula)
may work

 

[CaptainGravyBum]

FYI I managed to find a solution which involved stripping the time value only from one of the columns, because it had both date and time.