By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,940 Members | 1,391 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,940 IT Pros & Developers. It's quick & easy.

Summing Time in Hours (Acc 03)

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
Oops, what I gave you actually doesn't work if the total time goes over 24
hrs. Try this instead:

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.