Calculating Hours Worked

[andy101]

I have a form which allows the user to enter a start time and an end time for an appointment. I have a further field where i want to display the total number of hours worked. i've tried [end time] - [start time] but this does not work, i think this is due to the layout of the date e.g. 10:00. Does anyone know any code or a formula to calculate the number of hours worked?

 

[axa]

andy101,

the DateDiff() function should be the answer to your needs. It returns the time difference between two Date/Time values, with a time interval unit you specify...

lngTimeDiference = DateDiff(<interval code>,<DateTime1>,<DateTime2> )

where <interval code> could be "h" for hours or "n" for minutes (not m ! M will return the month difference when working with dates!)

if you just want whole hours, use "h". however if it is likley you will encounter partial hours it would be best to use minutes("n") where 90 mins = 1.5 hrs...

something like this...

lngTimeWorked = DateDiff("n",Date1,Date2)

Remember in this case lngTimeWorked is in minutes.

you could use a function like the one bellow to translate the minutes value into a string like "1 Hrs, 30 mins"

Function Mins2Hours(strTimeWorked as String)As String
    Dim lngHours as Long
    Dim lngMins as Long
    lngHours =     lngTimeWorked mod 60
    lngMins = lngTimeWorked - (lngHours * 60)

    Mins2Hours = lngHours & " Hrs, " & lngMins & " mins"
End Function


Hope that helps

axa