# How to count an average date?

My accounts have the Date field Activity__c. How can I figure out an average date between all these Activity__c fields and define the closest date to the average date?

I tried to use SOQL: [SELECT AVG(Activity__c) FROM Account WHERE Activity__c != null]

But it shows an error: field Activity__c does not support aggregate operator AVG.

Now I know that aggregate function average doesn't work with Date. Could somebody help me, please?

• You can make a formula, which is equal to duration between certain date and Activity__c date in days. Then you can count average of that formula, and calculate which day it is Jan 23, 2021 at 21:24
• thank you, I did it ) Jan 23, 2021 at 23:36

## 1 Answer

I did it:

//method finds closest date to average date

``````private static Date findClosestDateToAvg(){
List<Date> dates = new List<Date>();
for (Account acc : [SELECT Activity__c
FROM Account
WHERE Activity__c != NULL]) {
dates.add(acc.Activity__c);
}

Date avgDate = calcAvgDate();
Date closestDate = findMinDate();
for (Date d : dates) {
if (Math.abs(d.daysBetween(avgDate)) < Math.abs(closestDate.daysBetween(avgDate))) {
closestDate = d;
}
}
return closestDate;
}
``````

// method calculates average date in fileds Activity__c of Account

``````private static Date calcAvgDate() {
Date minDate = findMinDate();
Date maxDate = findMaxDate();
Integer diff = minDate.daysBetween(maxDate);
return minDate.addDays(diff/2);
}

private static Date findMinDate(){
return (Date)[SELECT MIN(Activity__c)minDate FROM Account][0].get('minDate');
}

private static Date findMaxDate(){
return (Date)[SELECT MAX(Activity__c)maxDate FROM Account][0].get('maxDate');
}
``````
• well, average is not a middle between max and min date. Consider you have 3 records with january 1, and 1 day with May 31. Will average be march 1 or january 31? Also, if you have more than 50k of records to query, you will hit the limit if you query all the accounts. Jan 24, 2021 at 10:30
• Thank you for the notice. Probably here is a problem with my English) I heeded to find the closest date to the middle date. Jan 25, 2021 at 6:51