|
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. |
compare TIME in IF statement
Hi,
How about this: Sub Macro4() If Range("A1").Value = TimeSerial(9, 30, 0) Then _ MsgBox Range("A1").Value End Sub BTW if you cross-post, chances are that you don't see some replies or people post their replies when you already got an answer, i.e. waste their time. Regards, KL <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. |
compare TIME in IF statement
thanks. alot,
pretty new to usenet,,, so is it ok to cross post by attaching the next group to CC? one minor question. how do you include special characters in formula? i am tryign to achieve something like 9: 30. with the semi-colon separating hours and mins. Range("N2").FormulaR1C1 = "=hour(rc[-1])& ':' &minute(rc[-1])" "KL" wrote in message ... Hi, How about this: Sub Macro4() If Range("A1").Value = TimeSerial(9, 30, 0) Then _ MsgBox Range("A1").Value End Sub BTW if you cross-post, chances are that you don't see some replies or people post their replies when you already got an answer, i.e. waste their time. Regards, KL <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. |
compare TIME in IF statement
Use double quotes instead of apostrophes.
Range("N2").FormulaR1C1 = "=HOUR(RC[-1]" & ":" & MINUTE(RC[-1])" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com <a wrote in message ... thanks. alot, pretty new to usenet,,, so is it ok to cross post by attaching the next group to CC? one minor question. how do you include special characters in formula? i am tryign to achieve something like 9: 30. with the semi-colon separating hours and mins. Range("N2").FormulaR1C1 = "=hour(rc[-1])& ':' &minute(rc[-1])" "KL" wrote in message ... Hi, How about this: Sub Macro4() If Range("A1").Value = TimeSerial(9, 30, 0) Then _ MsgBox Range("A1").Value End Sub BTW if you cross-post, chances are that you don't see some replies or people post their replies when you already got an answer, i.e. waste their time. Regards, KL <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. |
compare TIME in IF statement
ya, i tried tat,
VBA would give me a "expected: line nubmer or label or statement or end of statement" compile error. i guess its bcos, by using double quotes, we break the formula up into 3 parts. "Chip Pearson" wrote in message ... Use double quotes instead of apostrophes. Range("N2").FormulaR1C1 = "=HOUR(RC[-1]" & ":" & MINUTE(RC[-1])" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com <a wrote in message ... thanks. alot, pretty new to usenet,,, so is it ok to cross post by attaching the next group to CC? one minor question. how do you include special characters in formula? i am tryign to achieve something like 9: 30. with the semi-colon separating hours and mins. Range("N2").FormulaR1C1 = "=hour(rc[-1])& ':' &minute(rc[-1])" "KL" wrote in message ... Hi, How about this: Sub Macro4() If Range("A1").Value = TimeSerial(9, 30, 0) Then _ MsgBox Range("A1").Value End Sub BTW if you cross-post, chances are that you don't see some replies or people post their replies when you already got an answer, i.e. waste their time. Regards, KL <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. |
compare TIME in IF statement
Hi,
since time serial number is floating point number, you should not compare time serial numbers simply. I recommend to compare strings or integers. Sub Test() Range("A1").Value = TimeSerial(1, 40, 0) 'This shows False MsgBox Range("A1").Value = TimeSerial(1, 40, 0) 'compare strings MsgBox Format(Range("A1").Value, "hh:nn") = "01:40" 'compare total minute MsgBox CLng(Range("A1").Value * (24 * 60)) = 1 * 60 + 40 'calculate the difference If Abs(Range("A1").Value - TimeSerial(1, 40, 0)) < TimeSerial(0, 0, 1) Then MsgBox "same" End If End Sub -- hth okaizawa 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.. |
compare TIME in IF statement
Maybe you want:
Range("N2").FormulaR1C1 = "=HOUR(RC[-1])&" & """:""" & "&MINUTE(RC[-1])" a wrote: ya, i tried tat, VBA would give me a "expected: line nubmer or label or statement or end of statement" compile error. i guess its bcos, by using double quotes, we break the formula up into 3 parts. "Chip Pearson" wrote in message ... Use double quotes instead of apostrophes. Range("N2").FormulaR1C1 = "=HOUR(RC[-1]" & ":" & MINUTE(RC[-1])" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com <a wrote in message ... thanks. alot, pretty new to usenet,,, so is it ok to cross post by attaching the next group to CC? one minor question. how do you include special characters in formula? i am tryign to achieve something like 9: 30. with the semi-colon separating hours and mins. Range("N2").FormulaR1C1 = "=hour(rc[-1])& ':' &minute(rc[-1])" "KL" wrote in message ... Hi, How about this: Sub Macro4() If Range("A1").Value = TimeSerial(9, 30, 0) Then _ MsgBox Range("A1").Value End Sub BTW if you cross-post, chances are that you don't see some replies or people post their replies when you already got an answer, i.e. waste their time. Regards, KL <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. -- Dave Peterson |
compare TIME in IF statement
You don't need a formula that complex. It is all one string - no variable
value is being inserted Range("N2").FormulaR1C1 = "=HOUR(RC[-1]) & "":"" & MINUTE(RC[-1])" works just fine. -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... Maybe you want: Range("N2").FormulaR1C1 = "=HOUR(RC[-1])&" & """:""" & "&MINUTE(RC[-1])" a wrote: ya, i tried tat, VBA would give me a "expected: line nubmer or label or statement or end of statement" compile error. i guess its bcos, by using double quotes, we break the formula up into 3 parts. "Chip Pearson" wrote in message ... Use double quotes instead of apostrophes. Range("N2").FormulaR1C1 = "=HOUR(RC[-1]" & ":" & MINUTE(RC[-1])" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com <a wrote in message ... thanks. alot, pretty new to usenet,,, so is it ok to cross post by attaching the next group to CC? one minor question. how do you include special characters in formula? i am tryign to achieve something like 9: 30. with the semi-colon separating hours and mins. Range("N2").FormulaR1C1 = "=hour(rc[-1])& ':' &minute(rc[-1])" "KL" wrote in message ... Hi, How about this: Sub Macro4() If Range("A1").Value = TimeSerial(9, 30, 0) Then _ MsgBox Range("A1").Value End Sub BTW if you cross-post, chances are that you don't see some replies or people post their replies when you already got an answer, i.e. waste their time. Regards, KL <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. -- Dave Peterson |
compare TIME in IF statement
Yes, it does.
Thanks, Tom. Tom Ogilvy wrote: You don't need a formula that complex. It is all one string - no variable value is being inserted Range("N2").FormulaR1C1 = "=HOUR(RC[-1]) & "":"" & MINUTE(RC[-1])" works just fine. -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... Maybe you want: Range("N2").FormulaR1C1 = "=HOUR(RC[-1])&" & """:""" & "&MINUTE(RC[-1])" a wrote: ya, i tried tat, VBA would give me a "expected: line nubmer or label or statement or end of statement" compile error. i guess its bcos, by using double quotes, we break the formula up into 3 parts. "Chip Pearson" wrote in message ... Use double quotes instead of apostrophes. Range("N2").FormulaR1C1 = "=HOUR(RC[-1]" & ":" & MINUTE(RC[-1])" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com <a wrote in message ... thanks. alot, pretty new to usenet,,, so is it ok to cross post by attaching the next group to CC? one minor question. how do you include special characters in formula? i am tryign to achieve something like 9: 30. with the semi-colon separating hours and mins. Range("N2").FormulaR1C1 = "=hour(rc[-1])& ':' &minute(rc[-1])" "KL" wrote in message ... Hi, How about this: Sub Macro4() If Range("A1").Value = TimeSerial(9, 30, 0) Then _ MsgBox Range("A1").Value End Sub BTW if you cross-post, chances are that you don't see some replies or people post their replies when you already got an answer, i.e. waste their time. Regards, KL <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. -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 08:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com