EOMONTH() function and arrays ... seems like a bug

Anonymous
2020-01-31T20:03:46+00:00

In cell A1 enter =SEQUENCE(1,3,DATEVALUE("1/1/20"),1). This will create a spilled array for 1/1/20, 1/2,20, 1/3/20.

In cell A2 enter =EOMONTH(A1#) and you will receive a #VALUE error.

However, if you go into the formula editor (F2) for cell A2 and highlight A1# and press F9 you will get the array {43831,43832,43833} and then if you hit enter you will get a spilled array starting in A2 showing 1/31/20, 1/31/20 and 1/31/20 which is the result originally expected. 

This seems like a bug to me since EOMONTH will take the array if it is has manual values but will not take the array if it is a spilled array reference even though the spilled array results in the same values as the manual array.

ps. Please ignore the context (i.e. why would you want the EOMONTH for 3 consecutive days). The actual spilled array in A1 is giving me the first of each month over a number of months but the simple formula I provided above is easier to understand for demonstration purposes)

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

21 answers

Sort by: Most helpful
  1. Anonymous
    2020-02-01T14:32:23+00:00

    Hi.  In vba, we sometimes have to force an evaluation on these functions, because in vba, inputs are not really "dynamic"

    We need to force an evaluation on some array formulas on the worksheet as well.  For simplicity, we can force an evaluation with

    the old Array trick of "--"

    Out of habit, I like to use:

    =EOMONTH(0+A1#,0) 

    because this works better in some advanced single-formula stuff.

    With arrays, a "technique" is to check with different data (ie different months) to test.

    This is because sometimes we "think" something is evaluated, but it isn't.  ( I'll post an example later).

    By changing the month, we see that it appears to work.

    With the "new" way to do it, I hope these issues gets fixed in the future.

    >  ... ps. Please ignore the context . array in A1 ...... is easier to understand for demonstration purposes) 

    I would like to say "Thank you"  :>)

    So much nicer than the   AK999:AK10100 = Z100 stuff (general idea) that gets posted.   :>)

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments No comments
  2. Lz365 38,211 Reputation points Volunteer Moderator
    2020-02-02T10:31:31+00:00

    Quick sanity check with v2001 build 12430.20184 from Jan 30th => same results as previous

    @Dana

    Not sure if this is you who deleted your post with a very interesting example, if not let me know and I'll undelete it as I believe it should remains in this case, at least for The first "obvious" way might be to just use the column value, since it will return 1,2,3

    (Quick explain. re. why I tested/used TRANSPOSE. I was working on something else (Power Query) where transposing data was required. I quickly checked if this could do something for this issue and switched back to the Power Query case)

    TRANSPOSing is costly. However it confirms - if necessary - what Dana underlined with In vba, we sometimes have to force an evaluation on these functions, because in vba, inputs are not really "dynamic". We need to force an evaluation on some array formulas on the worksheet as well, even if not expected... 

    IHMO the key take away (after checking v2001 build 12430.20184 produces the same results) for those who would read/refer to this case is Dana's workarounds to force the evaluation of the array as they are cheap ones:

    Work as well with other functions, i.e. EDATE, WORDAY...

    Kudos to Dana

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments
  3. Lz365 38,211 Reputation points Volunteer Moderator
    2020-02-01T12:13:27+00:00

    Ko

    Interesting...

    In A1: =SEQUENCE(1,3,DATEVALUE("01/01/2020"))

    In A2: =EOMONTH(TRANSPOSE(A1#),0)

    Also works the other way round:

    =SEQUENCE(3,1,DATEVALUE("1/1/2020"))

    =EOMONTH(TRANSPOSE(A1#),0)

    EDIT Forgot: and of course a crazy

    =EOMONTH(TRANSPOSE(TRANSPOSE(A1#));0)

    Spills as you initially expected 

    Not sure what to think about this...

    EDIT2 Still with =SEQUENCE(1,3,DATEVALUE("01/01/2020")) in A1

    =DAY(A1#), =MONTH(A1#) => Work

    =EDATE(A1#,1) => #VALUE (same with F2+F9)

    =WORKDAY(A1#,3) => #VALUE (same with F2+F9)

    =EDATE(TRANSPOSE(A1#),1) => Work

    =WORKDAY(TRANSPOSE(A1#),3) => Work

    Hummm...I now tend to think - so to agree with you - something is going wrong somewhere or if there is a reasonable explanation to this I'm eager to hear it

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments
  4. Anonymous
    2020-02-01T16:16:08+00:00

    Thanks all for you know-how and input.

    I think there will be more discussions around dynamic arrays as many times they do not seem to work as a reasonably intelligent Excel power user would expect.

    Was this answer helpful?

    0 comments No comments
  5. Lz365 38,211 Reputation points Volunteer Moderator
    2020-02-01T09:02:08+00:00

    Hi Ko

    Very interesting and TBH I would have expected EOMONTH to also return a #VALUE! error with array {43831,43832,43833} since (let's forget dynamic arrays a few mins) the function does not "accept" a range on values...

    Enter any date in A1 & A2 > in A3 =EOMONTH(A1:A2,0) > Enter => As expected #VALUE!

    Select A3 > F2 + F9 => still #VALUE!

    and it was my understanding that EOMONTH only accepts a single value as 1st argument/parameter but must admit that your demo raises questions with regard to the "impact" of dynamic arrays on some functions

    Not sure MSFT will see that one as a bug as there might be an (in)comprehensive explanation :) but feel free to raise this to their attention via File > Feedback > Send a Frown

    I suggest you stay subscribed to that thread just in case one would have a reasonable explanation to this. Anyway, thanks for raising that case. I think we're not finished yet with these dyn. arrays...

    Was this answer helpful?

    0 comments No comments