Averaging multiple dates

[Guest]

I have a query that returns multiple dates and I want to find the average
date... How can I do this?

I tried to use the ToOADate() to get the total of the dates as a double,
then divide by the number of dates and use FromOADate() to get back to a
date, but it does not produce the desired results (as I expected, but still
was hopefull it might).

 

[Raterus]

Can you give me an example with a few dates, and tell me what your "desired" results would be? Using ToOADate/FromOADate seems like a really good idea to me, so I'm wondering what problem you were having with it.

--Michael

 

[Guest]

10-29-2004
11-03-2004
11-03-2004
11-01-2004

I expect something like 11-01-2004 for the average of these dates. When I
use the method we are talking about, I get 11-19-1983 as the average... Here
is the code I am using:

Dim previousTask, x, y As Integer
previousTask = 1
x = 1
y = 0
Dim total As Long
total = 0
Dim average As Date
pathDataSet.Tables.Add(New DataTable)
pathDataSet.Tables(3).Columns.Add(New DataColumn("task_number",
System.Type.GetType("System.Int32")))
pathDataSet.Tables(3).Columns.Add(New DataColumn("task_date",
System.Type.GetType("System.DateTime")))
For i = 0 To pathDataSet.Tables(2).Rows.Count - 1
If previousTask = pathDataSet.Tables(2).Rows(i).Item("task_number") Then
total += CType(pathDataSet.Tables(0).Rows(i).Item("task_date"),
Date).ToOADate
x += 1
Else
average = Date.FromOADate(total / x)
pathDataSet.Tables(3).Rows.Add(pathDataSet.Tables(3).NewRow)
pathDataSet.Tables(3).Rows(y).Item("task_number") = previousTask
pathDataSet.Tables(3).Rows(y).Item("task_date") = average
y += 1
total = 0
x = 1
End If
previousTask = pathDataSet.Tables(2).Rows(i).Item("task_number")
Next

 

[Guest]

I may be doing something wrong in the code because it appears to work when
using hard-coded values. I will update this thread when I resolve or find
that I cannot resolve this issue. Thanks!

 

[Guest]

This method does work, but I had some errors in the code example. Sorry!