Convert xx:xx:xx to decimal value so can be summed.

[bUncE]

Morning all,

Im having some trouble when summing some time values from my Access Database.
The values are all "elapsed times" between one time and another so eg:
00:06:32
would mean a 6 minute and 32 second elapsed time.

The problem is that i cannot sum up these values, i just get a 0.
I thought i had it beat when i did a little loop as below..

Do Until IsEmpty(ActiveCell)
ActiveCell = TimeValue(ActiveCell)
ActiveCell.Offset(1,0).Select
Loop

This didnt work, not sure why but it makes the times a lot bigger than they
used to,
I know i can convert it inside a cell formula eg: ' = AY301 * 24 ' but i do
not want to do it like that, i would like the code to run on the
Worksheet_Activate so it simply converts the values when user selects the
worksheet.

Is this possible or should i just look at trying to use the formula route?

Any suggestions are much appreciated.

M

 

[Peter T]

Have a go with this -

Sub test()
Dim dt As Date
Dim rng As Range, cel As Range

Set rng = ActiveCell
If Len(rng.Offset(1, 0)) Then
Set rng = Range(rng, rng.End(xlDown))
End If

rng.NumberFormat = "hh:mm:ss"
For Each cel In rng
dt = -1

On Error Resume Next
dt = CDate(cel.Value)
On Error GoTo 0

If dt >= 0 Then
cel.Value = dt
End If
Next
End Sub

Regards,
Peter T

 

[bUncE]

Hi,

I managed to work it out, just had to do
CDate(TimeValue(ActiveCell)) * 24 which works fine!!
Just needed the CDate in there

Thanks for the reply!!

M