On Tue, 27 May 2008 14:38:14 -0700, T Lavedas wrote:
<snip
13:30 14:00 :30
14:00 13:30 :30
the all cells are formatted as general. the code snippets below are for
excel2000.

If .Cells(row, colSchedule).Value .Cells(row, colActual).Value Then
.Cells(row, colVariance).Value = .Cells(row, colSchedule).Value -
_
.Cells(row,
colActual).Value
Range(.Cells(row, colVariance)).NumberFormat = "hh:mm"

Else
.Cells(row, colVariance).Value = .Cells(row, colActual).Value - _
.Cells(row,
colSchedule).Value
Range(.Cells(row, colVariance)).NumberFormat = "hh:mm"
End if

<snip
TIA
bst

<snip

That might explain the problem you are having (or will have) in testing
against <30 or more than 30 minutes. There are two internal VBA
functions you probably want to understand: TimeValue() and DiffDate() in
order to perform this comparison. They might also apply to your problem
in subtracting the numbers in version 2000 (if it doesn't perform the
time conversion automatically, as appears to be the case from the type
mismatch problem). In that case, you might need to use something like
...

Sub test()
With ActiveWorkbook.Sheets(1)
.Cells(2, 3).Value = TimeValue(.Cells(2, 2).Value) -
TimeValue(.Cells(2, 1).Value)
.Cells(2, 3).NumberFormat = "hh:mm"
End With
End Sub

HTH,

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/

Thanks for your reply. I want to verify that i understand correctly. the
code is not necessarily wrong for what i want to do, but maybe excel2000
does not do what i want it to do. that is better to know. what bothers me
is that in the worksheet it will do the conversion automatically, and it
looks as if i will have to do them manually in the code. i will
investigate the two functions you mentioned.

do you think the comparison in the if statement is working correctly,
even though the math is not? i followup after i have attempted these
suggestions.

thanks

bst