# Average date ( day & month only ) for the last number of years using a dynamic range

#### Tikey

##### Board Regular
I use Excel 2019 from the Office Pro package.

I currently use the following formula to average a series of dates for just the day and month:

=ROUNDUP(AVERAGE(IF(ISNUMBER(C8:C38),DATE(1900,MONTH(C8:C38),DAY(C8:C38)))),0) - entered using Ctrl, Shift, Enter

This works fine, but I would like to take this a step further and adapt this to use in a dynamic range to cover the last 10 years.

I have tried editing this using the OFFSET function without any success, I am struggling with this, can anyone offer any suggestions.

Thanks in anticipation.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
make a named range of that range and use it in the formula

Thanks for your reply, unfortunately I would need further guidence if possible.

in the ribbon goto formula>define name, as new name give there "MyName" and refers to for example C8:C1000, later you can even make that name dynamic.
your formula
Excel Formula:
``=ROUNDUP(AVERAGE(IF(ISNUMBER(MyName),DATE(1900,MONTH(MyName),DAY(MyName)))),0)``

ADVERTISEMENT
Ok thanks, but I'm still confused, as although the range will be replaced by a named range, my thinking is the formula will still work the same and not dynamically for the last set number of years eg 10.

my range is named "last10" and refers to
Excel Formula:
``=OFFSET(Blad12!\$C\$8,MAX(0,COUNTA(Blad12!\$C\$8:\$C\$60)-10),,10,)``
testing.xlsx
BCDEFGHI
4
5=VERSCHUIVING(Blad12!\$C\$8;MAX(0;AANTALARG(Blad12!\$C\$8:\$C\$60)-10);;10;)
6=OFFSET(Blad12!\$C\$8,MAX(0,COUNTA(Blad12!\$C\$8:\$C\$60)-10),,10,)
7
82019
92020
102021the last 101st cell
1120222026\$C\$15
1220232027
1320242028
1420252029
1520262030
1620272031
1720282032
1820292033
1920302034
2020312035
212032
222033
232034
242035
25
26
Blad12
Cell Formulas
RangeFormula
F11:F20F11=Last10
H11H11=CELL("adres",Last10)
Dynamic array formulas.

ADVERTISEMENT
Thank you for your reply.

Whilst I don't fully understand it, I am wondering if my question wasn't very clear, as I cannot see average in the formula and the answer appears to be the year rather than the day and month.
Hopefully the following example will be clearer. Column C shows the dates to be averaged. Cell E13 shows the day / month average of all the data in Column C and Cell F14 the last 10 year ( C25:C34 ) day / month average of the data in Column C, this is the answer I am seeking. But, I wish the spreadsheet to respond dynamically so that when another date is added to C35, the answer in F14 will update automatically to the day /month average of cells C26:C35.

Hopefully I have made the question clearer.

#### Attachments

• Screenshot 2022-01-16 094212.jpg
125.9 KB · Views: 6
defined name = "mydates" with the formula in D3 without the leading '
columns D and E are only for check, you don't need them later.
G11 = today-10 years = all the dates later then that date are used. If you want it starting 1 january, just change the formula in G11
The value you want is G8, but is G10/G9, the same values as in cells D5:E5, for the check.
So those 2 were also only tempory. You can give G8 the numberformat "dd-mm" for example
Column C is filled with random dates starting 1/1/2010 until 31/12/2021. They change when the worksheet recalculates, so replace them with your fixed dates.

The average for all the dates is
Excel Formula:
``=SUMPRODUCT(DATE(0,MONTH(MyDates),DAY(MyDates)))/COUNT(MyDates)``

Map1
BCDEFGHI
2
3mydates=OFFSET(Blad6!\$C\$8,,,COUNT(Blad6!\$C\$8:\$C\$100),)
4
5317170
6checkcheck
7countdag
87/09/20201251avg231,2903226
923/01/2010023count31
1011/10/20100285sum7170
1127/06/20161179min date16/01/2012
1228/05/20141149
1325/04/20121116
1417/03/2015177
1523/05/20161144
1617/07/20141199
1725/03/2012185
182/12/20201337
1928/12/20151363
Blad6
Cell Formulas
RangeFormula
D5D5=SUM(D8:D100)
E5E5=SUMPRODUCT((E8:E100),D8:D100)
C8:C19C8=DATE(2010,1,RANDBETWEEN(1,12*365))
D8:D19D8=--(C8>\$G\$11)
E8:E19E8=DATE(0,MONTH(C8),DAY(C8))
G8G8=SUMPRODUCT(DATE(0,MONTH(MyDates),DAY(MyDates))*(MyDates>\$G\$11))/SUMPRODUCT(--(MyDates>\$G\$11))
G9G9=SUMPRODUCT(--(MyDates>\$G\$11))
G10G10=SUMPRODUCT(DATE(0,MONTH(MyDates),DAY(MyDates))*(MyDates>\$G\$11))
G11G11=EDATE(TODAY(),-10*12)

Last edited:
Thanks again, I think I am slowly understanding your solution.

A couple of questions if I could, what is Blad6! in the formula in D3, what is EDATE for in the G11 formula and how could I change that to 1 January if required ?

Apologies, I replied without thinking, Blad6! will be your sheet name I think.

Replies
19
Views
335
Replies
7
Views
217
Replies
12
Views
397
Replies
7
Views
251
Replies
5
Views
117

Threads
1,196,251
Messages
6,014,249
Members
441,808
Latest member
xplainer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back