After midnight

[RichGags]

Im sorry if this has been asked before, but I couldnt find it in the forums....

I have 2 time fields and a total hours field in my form. "START" "END" and "TOTAL HOURS". I needed to calculate the hours between start and end, so someone suggested that I use this as a control source in the "TOTAL HOURS" field:
=DateDiff("h",[start],[end]).

Works great, except if my event starts at 8pm and lasts till 1am. I get a result of 19 hours. How can I correct this AM/PM after midnight bug? Thanks.

 

[Pat Hartman]

You need to store date as well as time.

 

[RichGags]

I have a date field too called "BOOKINGDATE". How would I incorporate that into the datediff?

 

[R. Hicks]

Pat is absolutely correct in avoiding the problem.

If the Time values will never span more that 24 hours .. got to the link below for a work-around.

StartTime-EndTime Results in a Negative Number

RDH

 

[RichGags]

Ok, almost there....I put the code in my Code builder, but the field is giving me a #?Name error. This is what I pasted into my code builder.


Public Function fElapsedTime(vSTART As Variant, vEND As Variant) As String
Dim intHrs As Integer, intMins As Integer

If Not IsDate(vSTART) Or Not IsDate(vEND) Then Exit Function

If vSTART > vEND Then
intMins = DateDiff("n", DateAdd("d", -1, vSTART), vEND)
Else
intMins = DateDiff("n", vSTART, vEND)
End If

intHrs = intMins \ 60
intMins = intMins Mod 60

fElapsedTime = intHrs & ":" & Format(intMins, "00")

End Function


How and where do I reference the function in my Total Time field??