Search code examples
csvexcel-formulalibreofficelibreoffice-calc

libreoffice calc lookup: return value if date is between two dates


I have a list (csv) of payments (and their timestamps) from certain phone numbers and a separate list (also csv) of subscriptions, plus start and end dates of the subscriptions for said phone numbers.

However, several phone numbers have had 2 subscriptions historically (ie. started one, stopped it, started a new one) and I need to associate each payment with the right subscription.

Essentially, I need a way to do

"FOR this payment RETURN the subscription.id WHERE subscription.phonenumber=payment.phonenumber AND payment.timestamp IS BETWEEN sub.startdate AND sub.enddate".

How can I accomplish this with a formula? For those that only had a single subscription, I just did INDEX MATCH, however I can't see that working here


Solution

  • Let's say you put 2018-01-01 to A1 and 2018-12-31 to A2. Then the date to be tested (e.g. 2018-06-01) to A3. Put =IF(AND(A3>A$1;A3<A$2);"True";"False") to e.g. B3, this will give True. If you put 2019-06-01 to A3, it'll give you False.