Summing Time

[RangerFan]

My last question for today. Everybody here is so helpful.

I need to sum a column of times on a report and display a grand total on the report footer. Sum hasn't worked for me so far. The times print as follows on the report:

1:00
0:30
0:15
1:30

I need to total these.

As always, any help is appreciated.

 

[SkipVought]

Keep in mind that there's a fundamental difference between TIME (think of it as Time of Day) and DURATION.

You cannot add Times. What does it mean to add 6:00 AM plus 11:59 PM?

But you can add Durations or Time. and you can add Durations of Time to a Time Valuv or Time of Day.

Time is store as DAYS, actually FRACTIONS of a day.

Duration should not be stored as Time (time of day) but as Hours or minutes or Seconds. THEN, you can do math on them to your heart's delight.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[RangerFan]

My problem is that all I have to work with is the data in the column as is. Is there a way to convert the "1.30" to hhmm?

 

[SkipVought]

Well it is possible to add the values in the table.

Select SUM([My Time Field])
From [My Table]

ought to do it.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[ProgramError]

Suggest you convert to the hours ie before the :, to minutes. Multiply by 60 and add the minutes after the :. Ths will give your durations in mins which you then need to total again and convert back to HH:mm

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[SkipVought]

Ian,

To convert a TIME VALUE to minutes, all you need do is multiple the TIME VALUE by 24, as TIME VALUE units is Days and there are 24 hours in a day.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[RangerFan]

Thank you everyone for your help. For the moment, the solution to adding these numbers in an Access report has eluded me, however, I was able to get the results I needed in Excel. An agreeable work around for the moment.

Thanks again.