Home |
Search |
Today's Posts |
#1
|
|||
|
|||
compare time in IF statement
hi,
case 1 i have this column, with time in US locale and in the format H:MM , for example, 9:30 in cell M2 My VBA code tries to compare if range(M2).value = time(9,30,0) then DO SOMETHING just fails to work.. --------------------- case 2, when i tried to convert the whole column to text, 9:30 becomes 0.39583333, the time serial code. but its still useless when I write if range(M2).value = 0.39583333 then DO SOMETHING thnks. |
#2
|
|||
|
|||
Try something like
If Range("M2").Value = TimeSerial(9,30,0) Then ' do something End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com <a wrote in message ... hi, case 1 i have this column, with time in US locale and in the format H:MM , for example, 9:30 in cell M2 My VBA code tries to compare if range(M2).value = time(9,30,0) then DO SOMETHING just fails to work.. --------------------- case 2, when i tried to convert the whole column to text, 9:30 becomes 0.39583333, the time serial code. but its still useless when I write if range(M2).value = 0.39583333 then DO SOMETHING thnks. |
#3
|
|||
|
|||
One way:
Case 1: If Range("M2").Value = #9:30:00# Then Note that Time(9,30,0) gives a type mismatch error. You could use TimeSerial(9, 30, 0) instead: If Range("M2").Value = TimeSerial(9, 30, 0)Then Case 2: XL stores values to 15 digits of precision, so 9:30:00 = 0.395833333333333, which is why your comparison didn't work. Even then, small rounding errors make exact comparisons difficult. You might think of using something like: If (CDbl(Range("M2").Value) - 0.395833) < 1e-6 Then (1 second is a bit more than 1e-5. In article , <a wrote: hi, case 1 i have this column, with time in US locale and in the format H:MM , for example, 9:30 in cell M2 My VBA code tries to compare if range(M2).value = time(9,30,0) then DO SOMETHING just fails to work.. --------------------- case 2, when i tried to convert the whole column to text, 9:30 becomes 0.39583333, the time serial code. but its still useless when I write if range(M2).value = 0.39583333 then DO SOMETHING thnks. |
#4
|
|||
|
|||
I think I'd use:
If abs(CDbl(Range("M2").Value) - 0.395833)) < 1e-6 Then to cover both sides of 9:30 JE McGimpsey wrote: One way: Case 1: If Range("M2").Value = #9:30:00# Then Note that Time(9,30,0) gives a type mismatch error. You could use TimeSerial(9, 30, 0) instead: If Range("M2").Value = TimeSerial(9, 30, 0)Then Case 2: XL stores values to 15 digits of precision, so 9:30:00 = 0.395833333333333, which is why your comparison didn't work. Even then, small rounding errors make exact comparisons difficult. You might think of using something like: If (CDbl(Range("M2").Value) - 0.395833) < 1e-6 Then (1 second is a bit more than 1e-5. In article , <a wrote: hi, case 1 i have this column, with time in US locale and in the format H:MM , for example, 9:30 in cell M2 My VBA code tries to compare if range(M2).value = time(9,30,0) then DO SOMETHING just fails to work.. --------------------- case 2, when i tried to convert the whole column to text, 9:30 becomes 0.39583333, the time serial code. but its still useless when I write if range(M2).value = 0.39583333 then DO SOMETHING thnks. -- Dave Peterson |
#5
|
|||
|
|||
yup - did it on one post, forgot on the other. Thanks.
In article , Dave Peterson wrote: I think I'd use: If abs(CDbl(Range("M2").Value) - 0.395833)) < 1e-6 Then to cover both sides of 9:30 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup and compare to (today) + time and get result | Excel Discussion (Misc queries) | |||
Compare 2 cells values with one IF statement | Excel Worksheet Functions | |||
If Statement - in time not decimal minutes | Excel Worksheet Functions | |||
unmet challenge | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |