performing calculations on time data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bst - 27 May 2008 21:04 GMT

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

Reply to this Message

Joel - 27 May 2008 22:21 GMT

I made some minor changes
1) You can't use ROW as a variable it is reserved. Change to RowCount
2) You didn'ty have a period in front of Range on the two lines with
Numberformat
3) Your Numberformat lines had Cells and Range. You can't have both

Sub test()

RowCount = 1
colSchedule = "A"
colActual = "B"
colVariance = "C"

With ActiveSheet
Do While .Range(colSchedule & RowCount) <> ""
If .Cells(RowCount, colSchedule).Value > _
.Cells(RowCount, colActual).Value Then
.Cells(RowCount, colVariance).Value = _
.Cells(RowCount, colSchedule).Value - _
.Cells(RowCount, colActual).Value
.Cells(RowCount, colVariance).NumberFormat = "hh:mm"
Else
.Cells(RowCount, colVariance).Value = _
.Cells(RowCount, colActual).Value - _
.Cells(RowCount, colSchedule).Value
.Cells(RowCount, colVariance).NumberFormat = "hh:mm"
End If
RowCount = RowCount + 1
Loop
End With

End Sub

> 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
[quoted text clipped - 51 lines]
> TIA
> bst

Reply to this Message

T Lavedas - 27 May 2008 22:38 GMT

> 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
[quoted text clipped - 51 lines]
> 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/

Reply to this Message

bst - 28 May 2008 02:24 GMT

<snip>

>> 13:30 14:00 :30
>> 14:00 13:30 :30
[quoted text clipped - 18 lines]
>> 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
[quoted text clipped - 18 lines]
> ===========
> 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

Reply to this Message

bst - 28 May 2008 17:37 GMT

> That might explain the problem you are having (or will have) in
> testing against <30 or more than 30 minutes. There are two internal
[quoted text clipped - 18 lines]
> ===========
> http://members.cox.net/tglbatch/wsh/

the timevalue function works great. i am ashamed that i did not find it
on my own. you would think that would show up somehwhere in the google
search. i did search the object browser but of course i forgot to simply
search help. i think it is the second function listed.

you were a great help.

thanks
bst

Reply to this Message