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

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" wrote in message
...
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

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

"Peter T" wrote:

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" wrote in message
...
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