How do a logical test on times in two different formats?
 

Help another brainlock here. Am trying figure out why this logical
comparison turns out false? I am comparing two different times one
being in format 0.206 (4:57 being the time) the other time is
MinFltArrival = "13:00". I know this is a unit conversion issue but
haven't figured out yet how to deal with it.

formatdatetime(cells(Rowx,8)) =0.206 (So 4:57 is in a decimal
format.)
MinFltArrival="13:00" (13:00 is in hh:mm
format)

the logical test.
? formatdatetime(cells(Rowx,8)) < format(MinFltArrival, "0.###")
False

The test should be coming out true because I was hoping to be
performing the IF statement of 04:57 < 13:00 and it should be YES. So
the question is how can I compare these two times the right way
considering one is in decimal format and the other is in hh:mm format?

Thanks,
Chet

How do a logical test on times in two different formats?
 

Hi Chet,

It shouldn't need any conversion. If A1 = 13:00 and B1 = .206 then an
IF test will show A1 to be Greater then B1. Excel stores 13:00 in
memory and the decimal for the time, so compares the two decimal
values.

Greg

On Apr 2, 10:18*am, Chet wrote:
Help another brainlock here. *Am trying figure out why this logical
comparison turns out false? * I am comparing two different times one
being in format 0.206 (4:57 being the time) *the other time is
MinFltArrival = "13:00". *I know this is a unit conversion issue but
haven't figured out yet how to deal with it.

formatdatetime(cells(Rowx,8)) =0.206 * (So 4:57 is in a decimal
format.)
MinFltArrival="13:00" * * * * * * * * * * * * *(13:00 is in hh:mm
format)

the logical test.
? formatdatetime(cells(Rowx,8)) < format(MinFltArrival, "0.###")
False

The test should be coming out true because I was hoping to be
performing the IF statement of 04:57 < 13:00 and it should be YES. *So
the question is how can I compare these two times the right way
considering one is in decimal format and the other is in hh:mm format?

Thanks,
Chet

How do a logical test on times in two different formats?
 

the format doesn't make a difference. excel staores dates and times as
number with 1.0 equaling one day and 1 hour = 1/24. there are three problems
with times

1) Make sure the cell is really stored as a number and not text.
2) Make sure you only have the time and not the date also. When you have
just a time you can format the cell as a number and the results will be under
1.0. If it includes the date the number will be much larger
3) Some times like 8:00 AM is a fraction like 8:00 AM = 8/24 =
..3333333333333333. Remember .33333 does not equal .33333333333333333. Make
sure the two times you are comparing are rounding to the same number of
decimal places

if you have a cell which is a time like 4:47 (say A1).

then use this

if Range("A1") < TimeValue("13:00") then

but if the 13:00 is in cell B2

if Range("A1") < Range("B2") then

"Chet" wrote:

Help another brainlock here. Am trying figure out why this logical
comparison turns out false? I am comparing two different times one
being in format 0.206 (4:57 being the time) the other time is
MinFltArrival = "13:00". I know this is a unit conversion issue but
haven't figured out yet how to deal with it.

formatdatetime(cells(Rowx,8)) =0.206 (So 4:57 is in a decimal
format.)
MinFltArrival="13:00" (13:00 is in hh:mm
format)

the logical test.
? formatdatetime(cells(Rowx,8)) < format(MinFltArrival, "0.###")
False

The test should be coming out true because I was hoping to be
performing the IF statement of 04:57 < 13:00 and it should be YES. So
the question is how can I compare these two times the right way
considering one is in decimal format and the other is in hh:mm format?

Thanks,
Chet