converting time

  • Thread starter Thread starter ChristieB
  • Start date Start date
C

ChristieB

In a text box there is a summed "HoursAndMinutes" total that looks like:
17:00 I need to convert that to 17.00 so I can multiply it by another field-
pay rate. Because when I try to put the two fields in [brackets], the
outcome is an #error
Am I doing something wrong? This is what my expression looks like:
=[totalhours]*[payrate] Please help.
 
Converting it to 17.0 will not do it. If it is an expression of time as
hours and mintues, you have to convert the minutes to a percentage of an
hour. For example, if you had 17:30, it would not be 17.30 it would be 17.50
that is
Minutes / 60. So, here is an expression that will do it.

=[payrate] * (Left([totalhours],2) + (Right([totalhours],2) / 60))
 
One way to calculate the pay due to the nearest minute would be

DateDiff("n",0,TimeValue(TotalHours)) * Payrate / 60

If you pay on the basis of quarter hours or half hours or whole hours
then you need to tell us the rules of rounding up / rounding down /
Partial period counts / partial period does not count / etc.

or since time is stored as decimal portion of datetime, you could use

CDbl(TotalHours) * Payrate

and probably get the answer you are looking for. But watch out for
small (very small) inaccuracies in the calculation.

I might try
CCur(TotalHours) * CCUR(PayRate) to get the most accurate number.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Back
Top