Totalling times in hours and minutes

[DJN]

I have a number of fields where the user enters the time it has taken to perform an action. At the moment, the time taken is a decimal figure and I then convert to hours and minutes in a function. It works, but is a bit cumbersome. I have tried changing the data type to short time, but the problem I have is that some actions can take more than 24 hours. Am I stuck with converting or is there a way I can total the hours and minutes taken in another way? Any advice appreciated.


David

 

[cable]

Would DateDiff work?
ie DateDiff("h",StartDate,EndDate) would return the total no of hours between the two date variables (which as you know can contain a date, a time or a date and a time)
The "h", means hours, you could also have, "n" for minutes, "s" for seconds, "ww" for weeks etc.

doh That's a utterly useless solution, forgot how to read!

 

[DJN]

Thanks Cable, but that won't work as you quite rightly say. I have no start or end times, just a figure. I am not interested in when they start or finish an action, merely the length of time it takes.Thank for reading the post anyway.

David

 

[Malcy]

Hi
I work a sort of similar database and I use a separate field for hours and minutes. So if I work for two and a half hours on a project I simply enter 2 and 30.
When it comes to invoicing I simply use a query to put the two together as a number on the basis [lngHrs]+([lngMins]/60). This gives me the result 2.5
Of course it is easy with half hours but when a job is only 25 minutes it is much easier to let Access do the maths.
The result can easily be added or multipled by an hourly rate.
Don't know if this is of any help to you as an approach but it might be
Best wishes and good luck

 

[cable]

I don't think there is an easy way to convert 1.5 to 1h 30mins...post what your currently using, maybe someone knows a more efficent or simpler method.

 

[Malcy]

Sorry but I don't have time to strip down the Db to show the query.
Why not use InStr function to say what is before the decimal point is the number of hours and the decimal point plus whatever is after it times 60 is the number of minutes.
That would be easy enough wouldn't it?
That would give you 1 hour and (0.5 * 60) for the minutes
QED surely - or am I missing something glaringly obvious?? If so, sorry!

 

[cable]

Sorry but I don't have time to strip down the Db to show the query.

oops, i meant DJN!

 

[The_Doc_Man]

The problem is that the Date data type is misleading and makes you think it is helpful. It is sometimes, but not in the way you wanted it to be for this particular problem.

You will need to continue to use a function when the times you are tracking are not date/time elements. In this case, what you describe is a resource like kilograms/pounds, dollars/euros, liters/quarts, etc. I.e. things that have an integer part and a fractional part.

When you add up hours and fractions like that, you are not using Access "native" Date/Time information. Native time format is days and fractions of a day, expressed as a Double (64-bit floating) number, since midnight of the reference date. For Windows, I believe the reference date is 1-Jan-1900, but don't make book on it. If I got it right, then midnight of 1-Jan-2000 was something like 36524.00. I've also heard of a 1-Jan-1970 reference but cannot recall the context. It might be some other application that uses the more recent number.

Because you aren't using "native" time format, none of the normal date/time functions will work correctly. Be prepared to "roll your own."

 

[Malcy]

No probs Cable
Easy done

 

[Pat Hartman]

DOC, the origin date(zero date) is 12/30/1899. Today is 38145.

DJN, I would either record the time as ElapsedMinutes only or use two separate fields - ElapsedHours and ElapsedMinutes. As others have alluded to, the date/time data type stores a specific point in time. It does not store elapsed time.

 

[Mike375]

In a telemarketing data base I have subtract the start time from finish time. I mutiply the result by 86400. Each record is added so I have both total time for all calls in a session and the time for each call

As an experiement I just entered 7am for start time and 3am for finish.

That gives -4 hours.

So I think if I factored the dates in and with some use of IIF I could get the right result if the time span crossed midnight.

If I wanted to convert 3.768 hours to hours and minutes then I think the Mid function would get the .768 into a field and I could multiply by 60 to get the minutes.

You can fiddle about with stuff forfever

Mike

 

[DJN]

Thanks for the replies folks. Sorry I have not replied earlier, but was 'let out for the day' Here is the function I am using to convert the time taken. Now, something very strange is happening. Say I am adding the contents of five fields, all .3 The function returns the correct value for the first 3 i.e. 90, but as soon as I try to add a 4th .3 it returns 80!!. Cannot for the life of me fathom out why. Can anyone shed any light on it?

Function ConvertToMinutes(Convert)

Dim Hours
Dim Minutes
Dim MinutesInHours
Dim TotalMinutes
Dim MyNumber


'Convert to whole number
Convert = Convert * 100

' Convert to a string, trimming extra spaces.
MyNumber = Trim(str(Convert))

'check that time is valid
If MyNumber = "0" Then
Exit Function
End If

'Find number of hours

Hours = Val(Left(MyNumber, Len(MyNumber) - 2))

'Convert to minutes
MinutesInHours = Hours * 60

'Find number of minutes
Minutes = Val(Right(MyNumber, 2))

'Add together
TotalMinutes = (MinutesInHours + Minutes)

'return result
ConvertToMinutes = TotalMinutes

End Function

 

[cable]

huh? whats your input supposed to mean?

If I put .5 in, I get 50 out?? Now I'd have said I should get 30? but thats assuming 1=1 hour.

 

[DJN]

Hi Cable. Yeah, that is right. The user puts in the figure say 8.3(8 hours 30 minues) and the function returns the complete number of minutes, 510 The users know to input the actual minutes and not its decimal equivalent. Seems to work so far. Except for the glitch when adding figures together after the 3rd figure?

So if I use the funcytion in the immediate window like thus:

?ConvertToMinutes(.3+.3+.3+.3) I get 80 instead of 120. Very weird.


David

 

[Jon K]

?ConvertToMinutes(.3+.3+.3+.3) I get 80 instead of 120. Very weird.

.3+.3+.3+.3 = 1.2, which, for your conversion function, is 1 hour 20 minutes, that is 80 minutes.

.

 

[Navyguy]

Just an observation...and I don't really have an answer to your problem...but

I recall when teaching navagation to junior officers, they always had a problem converting the decimal of hours to minutes. (don't really know why) When we do time/speed calculations we use what is called the 6 minute rule. I won't go into great detail but basically they needed to understand is that .1 hours equals 6 minutes, .2 hours is 12 munutes, etc to do their nautical mile calculation correctly.

Not knowing how accurate your measurement needs to be, by using your calculation and applying the "fixed values" of the 6 minute rule may be helpful.

Just some thoughts

 

[DJN]

Thanks Jon. I now realise the problem. The function does in fact give me the right answer for the figures entered. I will just have to work out another method.

David

 

[Navyguy]

Here's my 2 minutes worth!!

Just me being nosy again!!!

I am not sure if that would be considered a good practice to input hours/minutes as 1.3hrs = 1 hr 30 mins. I would think that most people would think that equals 1 hrs and 18 mins.

I suppose if your people are doing it now and they understand I guess that is ok, but then I would perhaps separate the minutes in the entry screen and then either combine it in the table or other reports.

Again maybe it is just my experience, but whenever I see a decimal of an hour I don't think of that as minutes but only 10ths of an hour. Not knowing how critical the actual minutes are to your application, maybe it really doesn't matter how accurate it is.

Cheers