I have imported a spreadsheet create in Microsoft Excel 2010 into Calc and most things work fine apart from 1 fairly critical thing. I have 1 sheets with a selection of date ranges identified, and another sheet with a single date and I am trying to determine what period this single date lies in.
This works perfectly in excel and I am able to get the expected output, something like:
Start Date | Week
2015-02-01 | 6
2015-02-14 | 8
The formula in the Week cell is:
=LOOKUP(2,1/((B2>=$'5 - Week Periods'.$B$2:$B$54)*(B2<=$'5 - Week Periods'.$C$2:$C$54)),$'5 - Week Periods'.$A$2:$A$54)
And the 5 - Week Periods sheet looks something like this:
Week | Start | End
1 | 12/27/2014 | 1/2/2015
2 | 1/3/2015 | 1/9/2015
3 | 1/10/2015 | 1/16/2015
However, when I try to run this in Libreoffice Calc (18.104.22.168), the calculcated week numbers work fine until I change the lookup date and then I get
It would appear that
(B2>=$'5 - Week Periods'.$B$2:$B$54)*(B2<=$'5 - Week Periods'.$C$2:$C$54) is returning 0.
Any ideas on how I can fix this or is there an alternate way I can get this to work?