compare TIME in IF statement

[Guest]

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.

 

[KL]

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

 

[Guest]

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])"

 

[Chip Pearson]

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

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])"

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

 

[Guest]

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.

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

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])"

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.

 

[okaizawa]

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

 

[Dave Peterson]

Maybe you want:

Range("N2").FormulaR1C1 = "=HOUR(RC[-1])&" & """:""" & "&MINUTE(RC[-1])"

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.

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

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])"




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.

 

[Tom Ogilvy]

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

Maybe you want:

Range("N2").FormulaR1C1 = "=HOUR(RC[-1])&" & """:""" & "&MINUTE(RC[-1])"

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.

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])"




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]

Yes, it does.

Thanks, Tom.

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

Maybe you want:

Range("N2").FormulaR1C1 = "=HOUR(RC[-1])&" & """:""" & "&MINUTE(RC[-1])"

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.

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])"




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.