This is a quick post to explain why EOMONTH doesn’t work with # references (dynamic ranges) and what to do about it.
When I started learning about dynamic arrays, I could see immediately how useful they would be to financial modelling. Even if you just use them to set up a sequence of dates.
For a start, there’s a function called SEQUENCE, which will generate sequences of numbers.
You might start building a model by setting up a variable for time periods and creating a SEQUENCE from that:
And then you might think: There’s a formula for calculating the end of month date using other numbers as a reference. This is EOMONTH and the syntax is:
EOMONTH(start date,months)
where the start date is expressed as a date and the months tells Excel how many months you want to add or subtract.
So in the example above, =EOMONTH(B4,D5) will give 31/03/2024.
But if you then want to alter the formula so that you reference the full sequence of numbers using # reference, you get an error:
The formula = EOMONTH(B4, D5#) should work. (In the same way that = COUNT(D5#) would work and would return the number 5)
There is no mention that EOMONTH doesn’t work with # referencing in the official Microsoft documentation about EOMONTH.
The reason why this doesn’t work is that not all of Microsoft’s formulas were made to retrospectively work with dynamic arrays. EOMONTH is one. EDATE has the same issues.
In other words, it’s not you. It’s Microsoft.
To fix it, bizarrely, the solution is just to add an operator to the hash reference. I worked this out by trial and error because it was perfectly happy giving me a sequence of years, using the formula EOMONTH(B4,D5#/12):
So if you want it do to months, you just need to add an operator that doesn’t change the result. For example
=EOMONTH(B4,D5#+0)
Bizarre?
Exceljet also gives some alternative ways to structure your formulas so that you don’t come across this problem.
This is an example of the sorts of issues I cover when training teams in Excel. I don’t just tell you about cool new formulas. I tell you how they can go wrong so you don’t get upset when they do.
If you want a quarterly newsletter summarising latest developments in Excel and other things I've found interesting or useful, please sign up here. I won't use your data for anything else.
Add one of the unary operators (double negative or single plus) before the dynamic range…
Using your example worksheet…
$D$5 : =SEQUENCE(,B3,0)
$D$6 : =EOMONTH(B4, –D5#)
$D$7 : =EOMONTH(B4, +D5#)
$D$8 : =EOMONTH(DATE(2024, 1, 2), SEQUENCE(,B3))
For EDATE –
$D$9 : =EDATE(B4, +D5#)
$D$10 : =EDATE(DATE(2024,1,2),+SEQUENCE(,B3,0))
Gives the spilled date ranges
Thanks! Looks like any kind of mathematical operator works to fix it.