7 Replies Latest reply on Jun 29, 2009 4:07 PM by jamie.hunt

# Calculate average time left between a set of dates.

### Title

Calculate average time left between a set of dates.

### Post

I hope someone can help me out. I'm not sure how easy this is or not.

I am looking for a calculation to find the average number of days between a set of dates. For example if I have a set of dates such as:

01/01/2009,

06/01/2009,

11/01/2009,

14/01/2009,

17/01/2009.

Time between the dates is

5 days

5 days

3 days

3 days

So the average time between dates is 4 days.

To put this into context this is so I can calculate the average time a Client leaves between their Appointments.

I have a table with Client profiles set up which hold client contact information (such as Name, Phone, Address...)

I have a table for client Appointments set up with appointment information (such as Date, Time, Name of client, The service they had...)

They are linked by a field "clientID" and in a layout for Client Profiles there is a portal showing all previous Appointments for which ever Client.

• ###### 1. Re: Calculate average time left between a set of dates.

I gather that each date is in a separate record?

I believe that (Latest Appointment - Earliest Appointment)/Number of appointment records will give you the same average value.

There are a number of ways to reference these three numbers in order to compute the average. The best method will depend on the structure of your database.

• ###### 2. Re: Calculate average time left between a set of dates.

Yes each date is a separate record in a table "appointments"

Each appointment is related to the client only by a "clientID"

which would be the best way to calculate the latest and earliest appointment? Perhaps something like a Get lowest date, Get Highest date? Which function would be best for this do you think?

• ###### 3. Re: Calculate average time left between a set of dates.

Min ( YourDate )

Max ( YourDate )

• ###### 4. Re: Calculate average time left between a set of dates.

Try:

`( Max ( Appointments:: Date ) - Min ( Appointments:: Date ) ) / ( Count ( Appointments:: ClientID ) - 1 )`

• ###### 5. Re: Calculate average time left between a set of dates.

Using the original sample data in the original  DD/MM/YYY format:

(17/1/2009 - 1/1/2009 - 1)/(5 -1)

Produces (16 days -1)/4  = 3.75 ?!!

Correct me if Im wrong, but I think there's an extra "-1" in the numerator there.

• ###### 6. Re: Calculate average time left between a set of dates.
Yes, it is (or was - I have edited it out). Thanks for noticing.
• ###### 7. Re: Calculate average time left between a set of dates.

Yes thats perfect when you get rid of one of the "-1"s.

Thanks very much guys!