Time calculations

[Guest]

I am trying to find a calculation to muliply the total hours (derived by
using 'HoursAndMinutes format), by a currency rate i.e £3.55, to give a total
wage. Even though I format the field as currency, because I am using the
'HAM' format, this overrides it and I get a result that is incorrect showing
as xx:xx.
Hope this makes sense!

Phil

 

[Wayne Morgan]

You need to modify the data for the calculation. Displaying the data as
hh:nn is fine, but as you noted, it doesn't work well in a calculation.

Options:
1) Convert the hh:nn value to decimal hours and do the calculation.

2) Calculate the rate per minute and do the calculation twice, once for the
minutes and once for the hours then add the results together.

3) Calculate the rate per minute, change hh:nn to minutes and calculate the
total.

 

[Guest]

Wayne
It's trying to convert the hours/min to decimal hours that I'm having
problems with, I have no trouble in Excel, but I can't crack the formula with
Access, could you clarify please

 

[Wayne Morgan]

I assume the field is a text field with a value in it such as 4:32 for 4
hours, 32 minutes. Where are you performing this calculation? If you want a
single command line, use a user defined function to change the value to
decimal hours.

Example:
Public Function HHMMToDecHours(strInput As String) As Currency
Dim strArray() As String
ReDim strArray(2)
strArray() = Split(strInput, ":")
HHMMToDecHours = (CInt(strArray(0)) * 60 + CInt(strArray(1))) / 60
End Function

You could then call it as:
=HHMMToDecHours([HHMMField]) * [RateField]