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) < 1e6 Then (1 second is a bit more than 1e5. 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)) < 1e6 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) < 1e6 Then (1 second is a bit more than 1e5. 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)) < 1e6 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 