469,347 Members | 19,798 Online

# Summing Time in Hours (Acc 03)

Hi All

I have an aggregate query that groups by each user, then calculates
Sum(TimeInvested). I need to display the total time (per user) in hrs / mins
e.g. 45:30 (= 45 hrs 30 mins).

Can someone help me with the function to so this?

thanks
paul
Sep 30 '06 #1
3 2049 On Sat, 30 Sep 2006 20:14:59 GMT, Paul Wagstaff wrote:
Hi All

I have an aggregate query that groups by each user, then calculates
Sum(TimeInvested). I need to display the total time (per user) in hrs / mins
e.g. 45:30 (= 45 hrs 30 mins).

Can someone help me with the function to so this?

thanks
paul
Sum the [TimeInvested] as minutes, i.e. 2 hours 25 minutes = 145
minutes.
Then divide [TimeInvested] by 60 for hours.
i.e. [TotalInMinutes])\60 = 145\60 = 2
(note the backward slash to return an integer value for hours.)

Then use [TotalInMinutes]) Mod 60
to get the remaining minutes.
145 Mod 60 = 25
You may need more than one query to do this.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Sep 30 '06 #2
What is the format of TimeInvested? If it is hours and decimal hours, like
5.3, then you could say:
TotalTime: Format(Sum([TimeInvested])/24,"Short Time")
If the field is in days and decimal days, like .5 for 12 hours, then you
would not divide by 24.
good luck
-John

"Paul Wagstaff" <pa**********@blueyonder.co.ukwrote in message
news:7f******************@fe1.news.blueyonder.co.u k...
Hi All

I have an aggregate query that groups by each user, then calculates
Sum(TimeInvested). I need to display the total time (per user) in hrs /
mins e.g. 45:30 (= 45 hrs 30 mins).

Can someone help me with the function to so this?

thanks
paul

Sep 30 '06 #3
Oops, what I gave you actually doesn't work if the total time goes over 24

TotalTime: Int(Sum([TimeInvested])) & ":" &
Round(60*(Sum([TimeInvested])-Int(Sum([TimeInvested]))))

again, assuming that your field holds the number of hours

"John Welch" <john(remove)welch@cal(remove)central.comwrote in message
news:ef*******@enews1.newsguy.com...
What is the format of TimeInvested? If it is hours and decimal hours, like
5.3, then you could say:
TotalTime: Format(Sum([TimeInvested])/24,"Short Time")
If the field is in days and decimal days, like .5 for 12 hours, then you
would not divide by 24.
good luck
-John

"Paul Wagstaff" <pa**********@blueyonder.co.ukwrote in message
news:7f******************@fe1.news.blueyonder.co.u k...
>Hi All

I have an aggregate query that groups by each user, then calculates
Sum(TimeInvested). I need to display the total time (per user) in hrs /
mins e.g. 45:30 (= 45 hrs 30 mins).

Can someone help me with the function to so this?

thanks
paul

Oct 1 '06 #4

### This discussion thread is closed

Replies have been disabled for this discussion.