MS Access find the Average difference between two dates

I've attached a sample database with useful date functions.  The normal DateDiff() function does not return the difference as hh:mm:ss.  You would do this by getting the difference in seconds using DateDiff() and then writing your own function to format the results however you want.  To get the average, you will need to get the difference using DateDiff() and average the seconds.  Then format the final result using your own function or the one included in the sample.
UsefulDateFunctions180618.zip

Can you give some info...maybe an example of what you are looking for.

Since you are only working with two dates your "average" will be the exact mid point between them.  This is accurate to the second.  We are finding the difference in seconds between the two dates, dividing it by 2 then adding that number to the start date.

The dates appear in the code as startdate, enddate, then startdate again for the Dateadd function.

SELECT DATEADD("S",DATEDIFF("S",'1/1/2019','12/31/2019')/2,'1/1/2019')

Select allOpen in new window

You can run a simple query like this:

Select 
    Year([LastDate]) As Year, 
    Month([LastDate]) As Month,
    CDate(Avg([LastDate]-[FirstDate])) As AverageTime
From
    YourTable
 Group By
    Year([LastDate]), 
    Month([LastDate])

Select allOpen in new window

Apply a format of hh:nn:ss to AverageTime.

If the average time can exceed 24 hours, you'll need a custom function like this for the display:

Public Function FormatHourMinuteSecond( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String
  
' Returns count of days, hours, minutes, and seconds of datTime
' converted to hours, minutes, and seconds as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03:55# + #20:01:24#
'   returns: 30:05:19
'
' 2014-06-17. Cactus Data ApS, CPH.

  Dim strHour       As String
  Dim strMinuteSec  As String
  Dim strHours      As String
  
  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute and second count when needed.
  strMinuteSec = Right("0" & CStr(Minute(datTime)), 2) & strSeparator & Right("0" & CStr(Second(datTime)), 2)
  strHours = strHour & strSeparator & strMinuteSec
  
  FormatHourMinuteSecond = strHours
  
End Function

Select allOpen in new window

and then modify the query:

Select 
    Year([LastDate]) As Year, 
    Month([LastDate]) As Month,
    FormatHourMinuteSecond(Avg([LastDate]-[FirstDate])) As AverageTime
From
    YourTable
 Group By
    Year([LastDate]), 
    Month([LastDate])

Select allOpen in new window

John - info

Call Date	Call Start Time	Call End Time
1/31/2019	3:51:10 PM	3:51:54 PM
1/31/2019	3:45:40 PM	3:50:53 PM
1/31/2019	3:30:47 PM	3:44:13 PM
1/31/2019	12:52:01 PM	1:04:15 PM
1/31/2019	12:47:07 PM	12:48:35 PM
1/31/2019	12:44:38 PM	12:47:03 PM
1/31/2019	11:12:43 AM	11:13:59 AM
1/31/2019	10:21:36 AM	10:22:29 AM
1/31/2019	9:59:11 AM	10:07:48 AM
1/31/2019	8:55:25 AM	8:56:17 AM
1/30/2019	4:15:27 PM	4:23:14 PM
1/30/2019	4:07:21 PM	4:35:54 PM
1/30/2019	3:31:22 PM	3:56:58 PM
1/30/2019	3:13:46 PM	3:21:32 PM
1/30/2019	2:59:00 PM	3:13:15 PM
1/30/2019	2:44:44 PM	2:46:58 PM
1/30/2019	2:38:16 PM	2:39:57 PM
1/30/2019	2:34:54 PM	2:42:35 PM
1/30/2019	2:26:54 PM	2:28:39 PM
1/30/2019	1:55:51 PM	2:01:41 PM
1/30/2019	1:12:44 PM	1:27:31 PM
1/30/2019	11:34:37 AM	11:42:31 AM
1/30/2019	11:28:18 AM	11:32:05 AM
1/30/2019	11:20:01 AM	11:25:33 AM
1/30/2019	11:05:47 AM	11:08:50 AM
1/30/2019	11:02:04 AM	11:03:58 AM
1/29/2019	1:51:58 PM	2:00:22 PM
1/29/2019	1:26:50 PM	1:29:09 PM
1/29/2019	12:13:26 PM	12:15:10 PM
1/29/2019	12:02:03 PM	12:09:16 PM
1/29/2019	11:27:33 AM	11:36:16 AM
1/29/2019	8:58:00 AM	9:07:24 AM
1/28/2019	3:59:35 PM	4:04:36 PM
1/28/2019	3:14:19 PM	3:24:25 PM
1/28/2019	3:07:44 PM	3:11:56 PM
1/28/2019	2:55:40 PM	3:01:23 PM
1/16/2019	2:16:40 PM	2:24:45 PM
1/16/2019	2:16:06 PM	2:23:59 PM
1/16/2019	10:45:52 AM	10:56:13 AM
1/15/2019	3:08:50 PM	3:10:30 PM
1/15/2019	1:58:57 PM	2:03:45 PM
1/15/2019	1:50:06 PM	1:52:16 PM
1/15/2019	12:08:00 PM	12:09:33 PM
1/15/2019	12:00:32 PM	12:04:11 PM
1/15/2019	11:58:03 AM	12:00:12 PM
1/15/2019	11:51:34 AM	11:55:31 AM
1/15/2019	11:10:58 AM	11:16:04 AM
1/11/2019	4:11:35 PM	4:17:06 PM

Select allOpen in new window

Try this, I can't test right not but I think it will work.

SELECT CONVERT(VARCHAR, dateadd(ms, 1000 * (DATEDIFF("S", [Call Start Time], [Call End Time])), 0), 114)
FROM MyTable

Select allOpen in new window

Bradley, that's T-SQL.
And there really is no reason to first convert to seconds.

I misread the original question; I thought they wanted to see this in HH:MM:SS format.  Sorry about the T-SQL; I'm jumping around and missed that this was Access.  Apologies.

SELECT formatelapsedtime(Round(Avg(DateDiff("s",[t].[Call Start Time],[t].[Call End Time])))) AS difavg, Round(Avg(DateDiff("s",[t].[Call Start Time],[t].[Call End Time]))) AS InSeconds
FROM Table1 AS t;

Select allOpen in new window

The query includes the formatted seconds and the average.  I just left the average to give me a sanity check for the formatted result.

Here's the function from the useful date functions database

Public Function FormatElapsedTime(ElapsedSeconds As Long) As String
Dim vHours As Long
Dim vMinutes As Long
Dim vSeconds As Long

vHours = ElapsedSeconds \ 3660
vMinutes = (ElapsedSeconds - (vHours * 3660)) \ 60
vSeconds = ElapsedSeconds - (vHours * 3660) - (vMinutes * 60)

FormatElapsedTime = vHours & ":" & vMinutes & ":" & vSeconds
End Function

Select allOpen in new window

Take a look at my attachment and especially my query Average
EDIT: i decided to change all the fields of the query to hh:mm:ss
so here is the revised SQL

SELECT Format(TimeSerial(0,0,Avg([S])),"hh:nn:ss") AS Average, Format(TimeSerial(0,0,Sum([S])),"hh:nn:ss") AS Total, Format(TimeSerial(0,0,Min([S])),"hh:nn:ss") AS Minimum, Format(TimeSerial(0,0,Max([S])),"hh:nn:ss") AS Maximum
FROM Base;

Select allOpen in new window

ExpertsExchange.accdb

I don't understand your question.  an average between 2 dates is endtime - start time.  You would need a collection of start and end times compute the difference between them, add the times together and divide it by the number of start times/end times collection.

Thanks to all