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.