Compare two dates periods to see if they have days in common

[Jeremy_Brunet]

:wave: Hello Airtable community!

Here’s my use case:

1. 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.

2. 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:

1. Invoice #1: Item A
2. Invoice #2: Items A & B
3. 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).

Cheers!

[ScottWorld]

This seems fairly straightforward. This same sort of a logic puzzle would apply to any sort of a rental business, such as a camera equipment rental business (which is a database system that I created for a large TV & film industry entertainment client of mine).

You essentially have 2 different requirements, and either one of them can be true in order for a particular item to be valid for a particular invoice:

1. The end date of the item has to be greater than or equal to the start date of the invoice AND the end date of the item has to be less than or equal to the end date of the invoice.
   OR
2. The start date of the item has to be less than or equal to the end date of the invoice AND the start date of the item has to be greater than or equal to the start date of the invoice.

So your formula could look something like this:

IF(
OR(
AND({Item End Date}>={Invoice Start Date},{Item End Date}<={Invoice End Date}),
AND({Item Start Date}<={Invoice End Date},{Item Start Date}>={Invoice Start Date})
),
"Valid Item","Invalid Item"
)

To further strengthen this formula to prevent against possible typos from your users, you may want to add in some additional checks, such as:

1. Making sure that all 4 fields are always filled in.
2. Making sure that Invoice End Date is always greater than or equal to Invoice Start Date.
3. Making sure that Item End Date is always greater than or equal to Item Start Date.

Hope this helps! :slightly_smiling_face:

p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with your system, please feel free to contact me through my website at ScottWorld.com.

[Jeremy_Brunet · Accepted solution]

ScottWorld:

IF(
OR(
AND({Item End Date}>={Invoice Start Date},{Item End Date}<={Invoice End Date}),
AND({Item Start Date}<={Invoice End Date},{Item Start Date}>={Invoice Start Date})
),
"Valid Item","Invalid Item"
)

Hi @ScottWorld, the logic was right with the combination of AND() nested in OR(), there’s just one missing AND() condition where the item start date is before or equal to the invoice start date && the item end date is equal or after the invoice end date:

IF(
OR(
AND({Item End Date}>={Invoice Start Date},{Item End Date}<={Invoice End Date}),
AND({Item Start Date}<={Invoice End Date},{Item Start Date}>={Invoice Start Date}),
AND({Item Start Date}<={Invoice Start Date},{Item End Date}>={Invoice Start Date}),
),
"Valid Item","Invalid Item"
)

Thanks for your help!