|
|
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. |
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. |
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. |
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 |
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 |
| All times are GMT +1. The time now is 03:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com