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" <(E-Mail Removed)> wrote in message
news:BCEED85A-910D-46F4-A7A1-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:BCEED85A-910D-46F4-A7A1-(E-Mail Removed)...
> > 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
> >
>
>
>