I noticed that if I try to use EOMONTH on more than one cell at a time on for instance:
| A | |
|---|---|
| 1 | 1/1/2022 |
| 2 | 1/2/2022 |
| 3 | 1/3/2022 |
| 4 | 1/4/2022 |
| 5 | 1/5/2022 |
| 6 | 1/6/2022 |
| 7 | 1/7/2022 |
| 8 | 1/8/2022 |
| 9 | 1/9/2022 |
| 10 | 1/10/2022 |
| 11 | 1/11/2022 |
| 12 | 1/12/2022 |
| 13 | 1/13/2022 |
If I use the following formula:
=EOMONTH(A1:A13,0)
I get a #VALUE! Error.
If I was to transpose the range (and transpose back to the original state) it works without errors:
=EOMONTH(TRANSPOSE(TRANSPOSE(A1:A13)),0)
Could someone explain this behaviour?
PS same goes for EDATE()
CodePudding user response：
=EOMONTH(A1:A13,0) is expecting a single date but it gets multiple dates so returns an error.
=EOMONTH(TRANSPOSE(TRANSPOSE(A1:A13)),0) works because the TRANSPOSE converts the range of data into an array first and it looks like EOMONTH can handle that so it outputs it as an array. It works the same if you use another function that converts the data to an array e.g. =EOMONTH(UNIQUE(A1:A13),0) also works.