Totalling Hours / Minutes

[STEVENCV]

Hi,

I am quite new to Access, so would like some help if you wouldn't mind. I have converted an Excel spreadsheet into an Access database.

The part I don't know how to do is adding up hours/minutes. My form looks like this: img189.imageshack.us/img189/3387/totalhoursfields.png

But there are actually 14 of those rows.

What I need to do is total up these 'Total Hours Provided' figures. I have tried researching it, and I believe I may have to create a new field, because our staff input hours AND minutes currently. For example, they will put "1 hour 5 minutes". If it makes it easier to total, I am able to create another field so that data is inputted in fields 'Total Hours' and 'Total Minutes'.

But I am still stuck with knowing how to add these together, and have it convert the minutes into hours.

Once I figure this out, I then need to start reporting on these figures, but that's a problem for a future thread!

I would really appreciate some help here, because I've been struggling with this for days.

 

[tehNellie]

Sub test()

  Dim hours As Integer
  Dim minutes As Integer
  Dim ExampleTotalMinutes As Integer
  
  ExampleTotalMinutes = 250
  
  hours = fix(ExampleTotalMinutes / 60) - fix ensures we round down i.e. 2.99 = 2
  minutes = ExampleTotalMinutes Mod (60)
  
  Debug.Print hours & " " & minutes
  

End Sub

Very basic example off the top of my head. (there may be better/more efficient ways of doing this)

Divide your total minutes by 60 into an Integer (whole numbers only) to get your Hours. Using Fix ensures that we round down to the integer value so 2.99 will round to 2.
Apply Mod(60) to the total minutes to get the remaining minutes.

also works as a query:

SELECT Fix(tbl.TotalMinutes/60) AS hours, tbl.TotalMinutes Mod (60) AS minutes
FROM tbl

 

[RainLover]

Life would be so much easier if your users entered Decimals.

e.g. .5 = 30 Mins

Each decimal place is worth 6 Minutes.

.9 = 54 Minutes etc.

This way you would only need the one field for entering Time, which then becomes very easy to Total.

Is this possible??

 

[STEVENCV]

Thank you for the quick reply. However, I am unsure where to put that code.

Do I create a field to for inputting minutes alongside the field for totalling hours (so that I input hours data into an hours field and minutes into a minutes field?
Then where do I put that code to add up the minutes into hours? After that, I need to add this total to the hours total (how do I add the hours up?) and show this as a 'x hours y minutes' total.

Does that make sense?

 

[STEVENCV]

RE: Decimals: That was my thought too, but this is going to be used by many different staff in different locations, and I honestly don't think they will get it right. To many of them will be putting 1.30 instead of 1.5 for 1 and a half hours.

I need to make it as simple for the user as possible. Unfortunately, I know that means making it harder to get it to work from a programming/design point of view.

 

[tehNellie]

The code was just to illustrate the method, on its own it doesn't do anything useful as such.

personally I probably wouldn't store hours and minutes separately, you've got two methods to convert hours and minutes into either Total Minutes and back again, or as a decimal value then you only need one field to store your time data in whichever format you prefer.

As illustrated you can put those calcs into a Query to format your TotalMinutes data so you don't need any code. If you have an hours + Minutes fields on your form, the query to combine them is equally simple

INSERT INTO tbl (totalMinutes)
VALUES((hours*60) + Minutes)

 

[RainLover]

RE: I honestly don't think they will get it right. To many of them will be putting 1.30 instead of 1.5 for 1 and a half hours.

I have been in the exact same situation on more than one occassion.

Don't underestimate the ability of others.
Most would prefer to type 1.5 rather than 1:30. Much faster to type.
If it doesn't balance with the hours supplied they will quickly find the reason for the difference.
If this is attached to Payroll the worker will complain so much that it will never happen again.

So I can't agree with your reasoning.

But if you are worried about it then use code to manipulate the data. It is the only other alternative I can think of.

 

[tehNellie]

I have been in the exact same situation on more than one occassion.

Don't underestimate the ability of others.

Don't over-estimate it either

I've used systems that take decimal values for times and I agree they're a pain in the backside 1.5 hours is 1:30 in my head, 1.25 = 1:15 and so on. Yeah you do eventually get used to it but I think any element of a system where your users have to get used to your way of doing things versus what they're expecting to do should give pause for thought.

 

[gemma-the-husky]

for inputting purposes, I would input time as "24 hour clock army time" without any punctuation

so 130 would be 1 hour 30 minutes
25 would be 25 minutes
203 would be 2 hours 3 minutes
172 would be rejected as illegal
100 would be one hour no minutes

if it matters force them to add leading zeroes, to force a four figure input.

you could probably do this with an input mask, also, although I ptrefer not to use input masks


after inputting it is then easy to check/validate, and THEN store it as a "true" date/time data type