# How to calculate the (average) number of days between records being created?

Say our Accounts can have Babies. I would like to make a report that shows how often our Accounts have Babies, on average.

So Account A has created a Baby in January and another Baby in June, thus on average A creates a Baby every 6 months.

(And then I want the average of all Accounts.)

Any idea how I could do this? The first step would probably be to look at the Babies' CreatedDate but I have no idea how to go from there..

You will need to create a report grouped by parent and add a summary field.

Assume today is 20 May 2021

Assuming that there is an account A which has 3 babies created on

1. 15 May - 5 days before today
2. 15 April - 35 days before today
3. 15 March - 65 day before today

So on average a child is created every 30 days.

Implementation:

1. Create a formula of type number field on Baby object which calculates date from today. (Days__c)
2. Create a report and group it via parent.
3. Add a grouping formula at Level 1 grouping.

`(Baby__c.Days__c:SUM - (Baby__c.Days__c:MIN * RowCount)) / RowCount`

Calculations based on above formula.

Baby__c.Days__c:SUM = 105 Baby__c.Days__c:MIN = 5 RowCount = 3

105 - 15 /3 = 30

This should give you a crude estimate.

Update: I revisited this and noticed that since you want to just calculate the average, you only require the highest and lowest value because the highest number is already sum of all values.

So the new formula would be.

`(Baby__c.Days__c:MAX - Baby__c.Days__c:MIN) / (RowCount - 1)`

So comparing 2 use cases, we have:

Use case 1:

1. Baby__c.Days__c:MAX = 65
2. Baby__c.Days__c:MIN = 5

Result = 30 (60/2)

Use case 2:

1. Baby__c.Days__c:MAX = 900
2. Baby__c.Days__c:MIN = 198

Result = 175* (702/4)

The division by total `RowCount` is not right, instead it should be `RowCount - 1` because we are looking to find intervals, which would always be 1 less than total rows

This would not work for 1 or 0 rows so that needs to be handled.

• Thanks for your message! So if we look at this case (i.imgur.com/jVWnRi4.jpeg), the result this gives me is 301 days between each. This doesnt sound quite right since the number of days between each Start Date was respectively 196+331+49+125 (= 701 days, divided by 5 Withdrawals = 140 days between each Withdrawal, not 301). It looks like the issue is that this also takes into account the 198 days between the last Withdrawal and the present day, which it shouldnt. If we remove that, then the formula gives 2301 - 1615 / 5 =137 days. Would you know how to ignore the last one? Commented May 20, 2021 at 13:50
• @user256515 I posted an update Commented May 20, 2021 at 14:24
• Yay this works perfectly, thank you very much! Also helped me familiarize myself with summary fields. Commented May 20, 2021 at 14:45