On May 27, 4:04 pm, bst wrote:
I have googled and searched this group and have not found exactly what i
am looking for. everything i have seen is for performing functions in
the worksheet and not in vba code.

i'll set up the prolbem, explain what i need, and give you what i have
come up with so far.
i have two columns with time data. i want the third column to show the
difference between the time. so if one 13:30 and the other is 14:00 i
want the 3rd column to show :30. or if one is 14:00 and the other is
13:30 i want the 3rd column to show :30
A B C
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

i have also attempted using Range(.cells(row,colSchedule).value etc....

in both attempts the comparison works fine (which boggles my mind),
however the cell assignment/mathematic operation fails with a mismatch
object error.

all cells formatted as general. i don't know if that is the problem, but
i doubt it since if i do this in the sheet, eg =B1-A1, i get no problem.

The code does not make it to the Range......NumberFormat so i have no
idea if that will work or not.

I do not think the builtin Time function(time(h,m,s) will work since i
cant seperate the information by hour.minutes. i have read how excel
stores time data, but i do not (hope not) think that applies.

what this is not is a payroll sheet or some sort of userform. based on
the if statements above some other things get processed and sorted out,
i have not included them for brevity.

there will also be a future comparison based on the result of the
calculation to the effect of if it is 30 then something happens, if it
< 30 something else happens.

i hope this is clear.

TIA
bst

This little test worked for me in Excel 2003...

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

Note that I removed the Range() reference as it was not valid in this
context. I also note that a simple subtraction in column C as a
worksheet function worked as well in Excel 2003. Excel automatically
converted the 14:00 and 13:30 to hh:mm format upon entry and performed
the correct math on the underlying time. When the format was adjusted
back to General, the values in the three cells were 0.5625, 0.5833 and
0.02083.

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/