Average dates

[MrsFrankieH]

Hello everyone!!

My computer is a laptop (ASUS)

My operating system is Windows 10.

I would like to know if it is possible to find a formula that will help me to figure out the average days, months and years between the following dates. I will be adding dates, so the formula would have to adjust to calculate additional dates. I appreciate your help in advance and I hope you all are safe and healthy.

Thank you.

 

[StephenCrump]

Yes, it's possible, but it depends what you mean by average days, months and years?

Here's a simple example with one guess. What answers are you expecting?

	A	B	C	D
1		Average	My Guess	You
2	5 May 2020	Days	233.5	??
3	3 Aug 2021	Months	7.5	??
4	15 Aug 2021	Years	0.5	??
Sheet1

Cell Formulas
Range	Formula
C2	C2	=(MAX(MyDates)-MIN(MyDates))/(ROWS(MyDates)-1)
C3	C3	=DATEDIF(MIN(MyDates),MAX(MyDates),"m")/(ROWS(MyDates)-1)
C4	C4	=DATEDIF(MIN(MyDates),MAX(MyDates),"y")/(ROWS(MyDates)-1)

 

[MrsFrankieH]

Hello Steven!! Thank you so much!! That's the thing, I'm sure of what answer I'm supposed to have as my math skills are dreadful but what I'm looking for is the average of all the displayed dates. Would "My Guess"be the averages of all the displayed dates?
Thankyou again, I appreciate your help more than I can express.

 

[MrsFrankieH]

Hello Steven. I'm getting errors. Does the date format matter? Thanks again and be safe.

 

[StephenCrump]

For my example: =AVERAGE(MyDates) will return 8 March 2021, the average of the three dates.

I'm not sure what you mean by errors? I have defined MyDates:=Sheet1!$A$2:$A$4. You could also use =AVERAGE(A2:A4)

 

[MrsFrankieH]

Hello Steven. I hope you had a great day today. What I meant by "errors" is 'error message'. Please forgive me as I have only rudimentary knowledge of Excel formulas or terminology so I'm not quite sure what "MyDates:=Sheet1!$A$2:$A$4" is. I copy and paste when I'm lucky enough to get a talented formula person as yourself to help me. When I copied and pasted your formula, for some reason I got an error message.

 

[StephenCrump]

I have attached my spreadsheet to illustrate: Average Dates

Formula 1 uses cell references A2:A6, which you are probably already familiar with.

Formula 2 uses a named range MyDates, which makes the formula easier to read. Have a look at Formula/Name Manager ... and you'll see that I have defined MyDates: =Sheet1!$A$2:$A$6. The quick way to do this is to use the Name Box. Select the cells A2:A6 and look in the Name Box (just above cell A1) and you'll see MyDates. You can create names by selecting the required cell(s), then simply typing in the Name Box the name you want to use for that cell(s).

If you can let us know how you want the results to be calculated, the forum can provide you with Excel formulas. But I am still not clear what answers you are expecting? What do you intend doing with the calculated average dates?

	A	B	C	D
1	MyDates	Average	Formula1	Formula2
2	5 May 2020	Days	208	208
3	3 Aug 2021	Months	6.75	6.75
4	10 Oct 2021	Years	0.50	0.50
5	1 Mar 2022	Date	12 Sep 2021	12 Sep 2021
6	15 Aug 2022
Sheet1

Cell Formulas
Range	Formula
C2	C2	=(MAX(A2:A6)-MIN(A2:A6))/(ROWS(A2:A6)-1)
D2	D2	=(MAX(MyDates)-MIN(MyDates))/(ROWS(MyDates)-1)
C3	C3	=DATEDIF(MIN(A2:A6),MAX(A2:A6),"m")/(ROWS(A2:A6)-1)
D3	D3	=DATEDIF(MIN(MyDates),MAX(MyDates),"m")/(ROWS(MyDates)-1)
C4	C4	=DATEDIF(MIN(A2:A6),MAX(A2:A6),"y")/(ROWS(A2:A6)-1)
D4	D4	=DATEDIF(MIN(MyDates),MAX(MyDates),"y")/(ROWS(MyDates)-1)
C5	C5	=AVERAGE(A2:A6)
D5	D5	=AVERAGE(MyDates)

Named Ranges
Name	Refers To	Cells
MyDates	=Sheet1!$A$2:$A$6	C2:D5

 

[MrsFrankieH]

Hello Stephen !!! Thank you so much for explaining the process to me and thank you for the spreadsheet. It looks GREAT and it looks very close to what I'm looking for, if not dead on!
What I'm looking for the calculations to do is to average the dates (days and months) between the top date (Thursday, November 9, 2006) to the bottom date (Monday, October 28, 2019). The purpose would be for me to have a guess as to what the next date would be (give or take ).
I'm so grateful for your help and grateful to you for sharing your immense talent with me. You are awesome!!

 

[StephenCrump]

The formulae in C2/D2 in Post #7 show the average period between dates so far as 208 days. So you are saying that your best guess for the next date is 15 Aug 2022 + 208 days = 11 March 2023.

Depending on what your dates represent, there may be better estimates, e.g is there an element of seasonality that should be allowed for?

 

[MrsFrankieH]

Hello Stephen!!Thank you so much!!!Yes, the average period (according to my math savvy, yet chronically busy sister) is 208 days. Your formula's exactly what I needed. I don't know if seasonality could make a difference, I'm not sure, what did you have in mind? I'm open to all of your suggestions.Thank you sooo much!! I really appreciate your skillful help.