Midpoint of Two Times?

[kestefon]

Hi,

Does anyone know a function I can use to calculate the midpoint between two times? (i.e. starting point, plus the total hours divided by 2)

Examples:


11PM to 4AM = 1:30AM

4PM to 5PM = 4:30 PM

7PM to 9:30PM = 8:15PM

[orange]

Something like (just an idea that you could flesh out)

MidPoint = Time1 + Datediff("seconds",Time2 -time1)/2

Note: I chose seconds, but you could use minutes... You could divide the seconds amount by 60 to get minutes.

Are you using Date/Time datatypes? Can you show details of your table design?

[kestefon]

I have two fields, one with the start time and another with the end time. e.g. "11:00:00 AM", "1:30:00 PM", etc.

Also, I gave the DateDiff function a try, but it doesn't seem to work if the End time is after 12AM. For instance, 9P to 12A is "-1260" minutes, instead of 180.

[orange]

Will get back a small test.

[orange]

Here's a small test.

Sub MyTestz()
Dim myStartDate As Date
Dim myEndDate As Date
Dim midpoint As Date

Dim DiffInSeconds As Long
 
myStartDate = #6/20/2014 9:00:00 PM#
myEndDate = #6/21/2014#  'Note this is 12:00:00 AM which is the start of the next day
 
DiffInSeconds = DateDiff("s", myStartDate, myEndDate)  ' the difference in the times in seconds (midpoint is half)
midpoint = DateAdd("s", (DiffInSeconds / 2), myStartDate) ' number of seconds to add to startdate
Debug.Print "MIdpoint is " & DateAdd("s", (DiffInSeconds / 2), myStartDate)
End Sub

One issue is that you should include the Date as well as the Time.
Also 9PM comes before 12AM on a shift end time, so the 12AM is really 12 AM tomorrow (or startdate + 1)

Note I used june 20 9:00 PM and June 21 12:00 AM, but vba editor/Access drops the time(it is assuming/defaulting to 00:00:00 AM)

Good luck.

[kestefon]

Thanks, but I don't know if that would really work for me, since I just have fields full of time values, not dates. Also, sorry, I should have specified, but do you know of a function I could apply in my query, not VBA?

Thanks!