Excel formula returns #VALUE! in Calc

[Rainmanne]

I have a strange problem with Calc. I have a file created in Excel. When I open it in Calc, one of the formulae on one sheet returns #VALUE! but works on the other sheet. If I try to copy it from the sheet, where it works or copy the entire sheet it stops working either. The parts of the formula also work but not in one and if I combine them in the third cell they do not return the result they should.

The formula is to find the end of a month under the following conditions (and I was helped with making this formula on one of the Excel help websites):
End of the month is normally a day before 25th, but if 25th falls on Saturday or Sunday, it should be a day before the prior Friday.

=SUMPRODUCT(IF(A1>EOMONTH(A1,-1)+24-LOOKUP(WEEKDAY(EOMONTH(A1,-1)+25,16),{1;2;3},{1;2;0}),{0,1},{1,0}),EOMONTH(A1,{-2,-1})+25-LOOKUP(WEEKDAY(EOMONTH(A1,{-2,-1})+25,16),{1;2;3},{1;2;0}))

where A1 is a today date (either =TODAY() or just a date).

Basically, if today is October 01, then the end of the month should be October 22.

To be absolutely honest, I do not understand, ho wth formula works (when I’ve got it, I spent an hour looking into it and figured it out, but after a year or so I forgot it.

I’ve got a feeling it something to do with the arrays but I am not sure because the parts of the formula work. So any help will be really appreciated.

[Lupp]

“…a day before 25th, …” Why not simply “24th”?
“…a day before the prior Friday.” Why not simply “the prior thursday”?
Did you just tell it the complicated way, or did you mean something else?

The formula itself is a typical “Write Only” formula. Even the original author might no longer understand it.
I would suggest you don’t try to get it work again as it is, but to develop a better one. To help you with this “project”, I would need an unambiguous explanation about what you actually want to achieve.

[Rainmanne]

Well, basically, if 25th is any workday, then EOM is 24th, if it’s Saturday or Sunday then it’s a prior Thursday. So it can never be Friday, Saturday or Sunday.

[Lupp]

As I understand the “…if it’s…” it refers to the 25th. This would imply: If 25th is a monday then EOM is the previous sunday.
I didn’t credit this interpretation as it seemed not plausible to me.
Please affirm that “precvious sunday” is correct in the mentioned case. I will then adapt the suggested solution.

[Rainmanne]

Sorry.

If 25th is Saturday, then EOM is Thursday
If 25th is Sunday, then EOM is Thursday
If 25th is Monday, then EOM is Sunday

Basically, the beginning of the month is always a weekday. It’s generally 25th but if 25th falls on a weekend, it’s a weekday before (Friday). End of the month could be Sunday.

[erAck]

It works if you enter the formula as array formula, i.e. close it with Shift+Ctrl+Enter instead of just Enter. Though for A1 today Oct-1 it returns Sep-25 instead of your example Oct-22. You may have different inline array row/column separators though, these are tricky in examples.

The formula returning error in non-array mode seems to be due to a difference in that Excel forces a (sub-)expression to array if any of its arguments is an inline array, there’s bug tdf#133260 for that.

[Rainmanne]

Oh, thanks a lot. I tried to enter the formula as an array and it didn’t work. But after reading your reply I tried again with the same negative result and then realised that the key combination for switching between keyboard layouts is set for Shift+Ctrl, which interfered with the Shift+Ctrl+Enter in Calc. As soon as I have changed it, I was able to enter the formula as an array one and it works now.

[Lupp]

A #VALUE! returned by a formula working in other places is often caused by a cell referenced by the formula expecting it to return a numeric value while the cell actually has only text to offer.

If the day acting as “My specific EndOfMonth” is expected to be

• the 24th of the respective month if not a saturday or a sunday
• the last thursday before the 24th otherwise,

a better (easily to understand) formula would be
=DATE(YEAR(EOMONTH(A1;0));MONTH(EOMONTH(A1;0));24)-CHOOSE(WEEKDAY(CURRENT();2);0;0;0;0;0;2;3)
(No array-evaluation needed…)

The formula may, of course, result in a friday. If this also should be avoided, the obvious adaption is to apply.

Please also consider Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet?.

===Edit 2020-10-02 about 10:45 UTC===

-1- I would like to simplify the above given formula to
=EOMONTH($A2;-1)+24-CHOOSE(WEEKDAY(CURRENT();2);0;0;0;0;0;2;3)

-2- Regarding the explanations given by the OriginalQuestioner the adapted formula would be
=EOMONTH($A2;-1)+24-CHOOSE(WEEKDAY(CURRENT();2);0;0;0;0;1;2;0)

This is also demonstrated in the attachment ask269022specialCalculatoryEOM_1.ods

[Rainmanne]

Thanks a lot for the answer. Sorry, I think I posted a slightly different formula. Just realised that I have two, which do pretty much the same, but one finds the end of the month based on the current date and another finds the end of the month based on the first date of the month (for historic calcs). So the explanation doesn’t match the formula itself.

In any case, the issue with the formula suggested is that the month runs usually from 25 till 24 of the next month. The formula works fine if the current day is any between 1 and 24. But if the current day is say 28th, the formula should return the end of the next calendar month, but it returns the and of the current calendar month (which is in the past). Sorry, if it’s a bit confusing.

Just an example. If the current date is September 28, 2020, the end of the month should be October 22, but the formula returns September 24, 2020. Probably, the original formula is that complex to handle it?