Hello Airtable community!
Here’s my use case:
I have a table Items where each items has a price and a date period. Given their period of validity, I can or cannot add the item to an invoice. Example: I can add item A anytime in the year as its validity starts 01/01 and ends on 12/31. Item B however is only available from 03/01 to 03/31.
I have a second table Invoices where each invoice covers a period of time, from a start date to an end date. Example: For customer A, I issue invoice #1 starting at 01/01 and ending at 02/15 and invoice B starting at 02/16 and ending at 04/30. For customer B, I only issue invoice #3 which starts at 02/15 and ends at 03/15.
So given these parameters, I should have these items in my invoices:
- Invoice #1: Item A
- Invoice #2: Items A & B
- Invoice #3: Items A & B
Does anyone have an idea how to write a formula that can check if the date period of each item overlaps with the date period of each invoice? The rule is as soon as they have one day in common, the item is eligible for the invoice.
If formulas can’t help here, I’m open to any another method that would do the trick (scripts for instance is fine too).