Averaging dates within each month.

[Rachelpan]

Column A has 12 months January through December.
Each month has several dates of deliveries.
I need a formula that will provide me with the average delivery date for each month and put the result in column B.

For example - column A goes from 2 to 200. All data is listed in date form (i.e. 1/12/2018) - January has 10 different delivery dates and 3 that are the same.
I would like all dates listed in January to be averaged and the result in column B (e.g. column heading "average by month" 1/13/2018 in B2; 2/4/2018 in B3, etc.).
Hope this makes sense.
Regards

 

[Toadstool]

Hi Racelpan,

You could add a column with month and year (just in case you ever have more than one year in the list) and then AVERAGEIFS the date for rows where it's the same month. As you can see my January 2019 average is 3am on the 17th.

Book1
	A	B	C
1	Delivery Dates	MY	Average
2	1/1/2019	1-2019	1/17/2019 3:00:00 AM
3	1/8/2019	1-2019	1/17/2019 3:00:00 AM
4	1/9/2019	1-2019	1/17/2019 3:00:00 AM
5	1/15/2019	1-2019	1/17/2019 3:00:00 AM
6	1/22/2019	1-2019	1/17/2019 3:00:00 AM
7	1/22/2019	1-2019	1/17/2019 3:00:00 AM
8	1/29/2019	1-2019	1/17/2019 3:00:00 AM
9	1/31/2019	1-2019	1/17/2019 3:00:00 AM
10	2/6/2019	2-2019	2/13/2019 3:25:43 AM
11	2/9/2019	2-2019	2/13/2019 3:25:43 AM
12	2/9/2019	2-2019	2/13/2019 3:25:43 AM
13	2/9/2019	2-2019	2/13/2019 3:25:43 AM
14	2/15/2019	2-2019	2/13/2019 3:25:43 AM
15	2/21/2019	2-2019	2/13/2019 3:25:43 AM
16	2/23/2019	2-2019	2/13/2019 3:25:43 AM
17	3/1/2019	3-2019	3/12/2019 6:00:00 AM
18	3/2/2019	3-2019	3/12/2019 6:00:00 AM
Sheet1

Cell Formulas
Range	Formula
B2:B18	B2	=MONTH(A2)&"-"&YEAR(A2)
C2:C18	C2	=AVERAGEIFS($A$2:$A$200,$B$2:$B$200,B2)

 

[Rachelpan]

Brilliant!!!
Many thanks
Regards

 

[Toadstool]

You're welcome!

 

[Rachelpan]

Back for a quick question.
When I may be looking at 3 or more years of data - column C would be rather lengthy.
Is it possible to have column C reference the average date in each month only once, rather than repeatedly?
Thank you
Regards

 

[Toadstool]

Let me give you two ways of doing this. For both ways I've changed the range to go down to row 9,999 so you may want to make a cup of coffee while it runs.

Option 1: Just check if the previous Month/Year matches this one and if so display null:

Book1
	A	B	C
1	Delivery Dates	MY	Average
2	1/1/2019	1-2019	1/17/2019 3:00:00 AM
3	1/8/2019	1-2019
4	1/9/2019	1-2019
5	1/15/2019	1-2019
6	1/22/2019	1-2019
7	1/22/2019	1-2019
8	1/29/2019	1-2019
9	1/31/2019	1-2019
10	2/6/2019	2-2019	2/13/2019 3:25:43 AM
11	2/9/2019	2-2019
12	2/9/2019	2-2019
Sheet1 (2)

Cell Formulas
Range	Formula
B2:B12	B2	=MONTH(A2)&"-"&YEAR(A2)
C2:C12	C2	=IF(B2=B1,"",AVERAGEIFS($A$2:$A$9999,$B$2:$B$9999,B2))

Option 2: Build a list of unique Month/Years (could be on another tab) and then do the AVERAGEIFS:

Book1
	F	G
1	Month/Year	Average
2	1-2019	1/17/2019 3:00:00 AM
3	2-2019	2/13/2019 3:25:43 AM
4	3-2019	3/12/2019 6:00:00 AM
5	4-2019	4/17/2019 4:48:00 AM
6	5-2019	5/18/2019 1:20:00 PM
7	6-2019	6/18/2019 3:25:43 AM
8	7-2019	7/11/2019 9:00:00 PM
9	8-2019	8/12/2019 6:51:26 AM
10	9-2019	9/16/2019 4:00:00 AM
11	10-2019	10/16/2019 9:00:00 AM
12	11-2019	11/6/2019
13
Sheet1 (2)

Cell Formulas
Range	Formula
F2:F13	F2	=INDEX($B$2:$B$9999,MATCH(0,INDEX(COUNTIF($F$1:$F1,$B$2:$B$9999),),0))&""
G2:G13	G2	=IF(F2="","",AVERAGEIFS($A$2:$A$9999,$B$2:$B$9999,F2))

 

[Rachelpan]

Both options look great.
I will try both.
Again, your quick response is greatly appreciated.
Regards
R.