Running sum of hrs:mins (sorry! plase be nice!)

[andagain]

Hi,
This is simple I know and has been covered many times I know, and I'd rather not incur the "check to FAQs" wrath of you guys, but if you can just let me know the way to go with this I'd appreciated it as I am just going round in circles.

My user enters a short-time into field 'duration'.
I just want to get a running total of duration. I have this as hours by:
Sum[Duration]*24
But how do I get a running total in hours and minutes from the durations that my user enters?

Thanks alot.

Andrew.

 

[traycee]

Hi,
What do you mean they enter a short-time into field duration. Do you mean they would enter something like 2:30 p.m. or 2 hours or 120 for minutes. Also, is this setup as a date/time field, number field, or other?

 

[andagain]

Ah yes, sorry,

They enter hh:mm into a date/time field called 'Duration'.
I then calculate:
sum[duration]*24
in a query to show a running total of hrs as a decimal in my form, but is there a way I can calc. this total to show the result as hrs and mins?

(It is easier for my users to enter a duration rather than start and end times).

Thanks,

Andrew.

 

[KenReay]

Hi

"I then calculate:
sum[duration]*24"

and you get the right answer?

my understanding of Date/Time fields is that they store a POINT in time as a number. The number is the number of days elapsed since a preset Date (Dec 31st 1899 I think) and number of seconds elapsed that day. So saving 2:30 in a date time fields saves a number of seconds equivalent to an elapsed time of Midnight (00:00:00) and 2:30 in the morning

What you need to do is to allow your user to enetr an elapsed time value (say 2:30) and to convert this to minutes (eg 150), save the minutes in the table and sum that

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[andagain]

HI ken,

Yes, when I sum the durations I get the right answer in days as decimal, and then *24 as hrs, but I understand what you say. The durations never go over 24 if that is the reason.

Say my user enters 2:30, meaning the duration (of an interview with a client) lasted for 2hrs 30mins, can't I just use an expression in my query to calculate this as 150 mins - how!? - (which I can then sum...but if I then have a total no. of minutes, how do I get Access to display this as hrs:mins in my form rather than hrs decimal????)

Sorry, I am not an Access developer and only ever dabble now and again to fix something I did badly ages ago....!

Thanks,
Andrew.

 

[PHV]

Something like this ?
minutes \ 60 & ":" & minutes mod 60

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[andagain]

Thanks PHV,

I now have this as the control source of the textbox on my form:

=([SumOfDuration]*1440)/60 & ":" & ([SumOfDuration]*1440) Mod 60

But this gives me the following displayed in my textbox:
hrs (as decimal) : mins

So say if my durations for this client are 01:00, 2:30 and 23:00, it displays:
26.5 : 30

Any ideas how can I get to show 26:30?

Cheers,

Andrew.

 

[PHV]

Replace this:
1440)/60
By this:
1440)\60

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[andagain]

Cool. Works now. Thanks alot PH!!!!!!!

One thing though, if the total comes to say, 121 mins, using this method is there a way to display it on the form as:

2:01

...rather than:

2:1
...as it currently does?

A.

 

[KenReay]

=([SumOfDuration]*1440)\60 & ":" & Format(([SumOfDuration]*1440) Mod 60,"00")




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

And doesn't this work ?
Format([SumOfDuration), "hh:nn")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[andagain]

Nice one Ken,

Works just fine. Thankyou.
And I've learnt something today.

Thanks PH!

Andrew.