Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I use 'CONTAINS' in an IF Logical Test? | Excel Worksheet Functions | |||
Logical test | Excel Discussion (Misc queries) | |||
Logical Test | Excel Worksheet Functions | |||
Logical test | Excel Programming | |||
Logical test | New Users to Excel |