Recording time spent

[KNap]

Hi



I have set up a case management system

at the end of a case record you can record the amount of time spent. I think at the moment it's not recorded as a number it's as short text.. not sure why - so the firsrt question is do i need to change this to a number?

the second question is how do I work out how much time I've spent during a date parameter? So I have created a report which lists each time spent record and I need the formula to add the time spent but in hours and minutes rather than a general number?

Happy to correct anything that I've done wrong to achieve what I need.

Thanks in advance

[Minty]

If you need to do maths on a number / datetime field (Which is actually stored as a decimal number as well) then you should store the data as the correct data type.

You can use the DateDiff() function to return a number in hours or minutes between two datetime values.
You can then convert that to display hours and minutes if you need it.

[KNap]

Thanks for this - just so I've got it right - I need to change [Time Spent] to a general number?

So what would the formula be to calculate the total [Time Spent] in a report footer please?

[KNap]

I mean converting the general number to hours and minutes?

[orange]

You record your time values in a common unit --say minutes.
You do your calculations knowing that:
60 min = 1 hour

Divide your minutes total by 60 to get Hours.

[KNap]

Thanks for this - however, on my report, no matter how I change the properties to display a general number with two decimal places i get a number with loads of decimal places....

The formular i used is this:
=sum([Time Spent]/60) & "hours"

is there something I'm missing?

Thanks

[orange]

Try

=Round(sum([Time Spent]/60),2) & "hours"

[KNap]

Great - that has got the decimals down to two - thank you....

However, the number that has come up is 10.67 - would that not be 11.07 in hours and minutes??

So sorry to be a pain

[Micron]

I'm kind of surprised that no one mentioned it's generally considered a bad idea to store calculations. If for example you have start and end time values as fields, you should be showing the elapsed time, not calculating and storing the result.

[KNap]

Ok - I'm up for all suggestions

[isladogs]

Great - that has got the decimals down to two - thank you....

However, the number that has come up is 10.67 - would that not be 11.07 in hours and minutes??

So sorry to be a pain

No.
10.67 hours =10 hr 40 min = 640 min

[Micron]

No.
10.67 hours =10 hr 40 min = 640 min

And the .67 is rounded?
Mathematically it's closer to .6665

not sure what this means or whom it's directed to

Ok - I'm up for all suggestions

If me and the notion of only calculating, start with a read of this:

http://allenbrowne.com/AppInventory.html

You can ignore the code for a stock count issue and just focus on the logic behind not storing calcs. In the end, your forms/reports just end up with unbound textboxes to calc the difference between 2 values, be they dates or whatever.

[KNap]

how would I go about doing that?

[orange]

Perhaps KNap can tll us more about the application generally.
I did not interpret the post to be storing a calculated field - however, that may be what is going on.
I was just showing the mechanics of calculating time duration --- hours/minutes; and Rounding.

[Micron]

@Orange, I may have misinterpreted this

at the end of a case record you can record the amount of time spent.

to be a calculation done by the db when in fact, someone might just be entering 1.5 hours as a value and this

how do I work out how much time I've spent during a date parameter

as a calculation that will shift the manual calculation to an automated one, but still storing the result. Perhaps not.