Want to flag overlapping dates AND times

Michelle T

Based on information I've found here, I've created a formula that identifies overlapping times that occur on the same day, but I'd like to adapt it to incorporate items that occur over multiple days. This will be used for booking of equipment.

Here is what I have so far:

=IF(COUNTIFS(SAP:SAP, SAP@row, [Start Date]:[Start Date], [Start Date]@row, [End Time]:[End Time], @cell >= [Start Time]@row, [Start Time]:[Start Time], @cell <= [End Time]@row) > 1, "No", "Yes")

"SAP" is the reference to the instrument. I'd like to add "End Date" to the formula. In the snip below, rows 1 & 2 should also be "no".

Thank you in advance!

Hollie Green

Ok I think I got it. It's due to the times you need to combine the dates and times to get it to consider both as a whole and not individually. So you will need a helper column for your start date/time and end date time. I titled mine as Start Date Time Helper and End Date Time Helper. If you title them the same you should be able to copy and paste formulas.

Start Date Time Helper

=IFERROR(VALUE(YEAR(DATEONLY([Start Date]@row)) + "" + IF(MONTH(DATEONLY([Start Date]@row)) < 10, "0") + MONTH(DATEONLY([Start Date]@row)) + IF(DAY(DATEONLY([Start Date]@row)) < 10, "0") + DAY(DATEONLY([Start Date]@row)) + IF([Start Time]@row < 1000, "0") + [Start Time]@row), "")

End Date Time Helper

=IFERROR(VALUE(YEAR(DATEONLY([End Date]@row)) + "" + IF(MONTH(DATEONLY([End Date]@row)) < 10, "0") + MONTH(DATEONLY([End Date]@row)) + IF(DAY(DATEONLY([End Date]@row)) < 10, "0") + DAY(DATEONLY([End Date]@row)) + IF([End Time]@row < 1000, "0") + [End Time]@row), "")

Formula to get your Yes/No's

=IF(COUNTIFS(SAP:SAP, SAP@row, [End Date Time Helper]:[End Date Time Helper], @cell >= [Start Date Time Helper]@row, [Start Date Time Helper]:[Start Date Time Helper], @cell <= [End Date Time Helper]@row) > 1, "No", "Yes")

Hollie Green

I think the issue may be [Start Date]:[Start Date], [Start Date]@row I think it needs to be [Start Date]:[Start Date], @cell>=[Start Date]@row or it will only count the Start date that is matching that start date exactly so it isn't count the cell that starts on June 14th as overlapping with the first one.

Michelle T

@Hollie Green The first two rows are now correctly flagged, but unfortunately other rows that should be OK are flagged as not OK (line 3 should be OK):

Hollie Green

Ok I think I got it. It's due to the times you need to combine the dates and times to get it to consider both as a whole and not individually. So you will need a helper column for your start date/time and end date time. I titled mine as Start Date Time Helper and End Date Time Helper. If you title them the same you should be able to copy and paste formulas.

Start Date Time Helper

=IFERROR(VALUE(YEAR(DATEONLY([Start Date]@row)) + "" + IF(MONTH(DATEONLY([Start Date]@row)) < 10, "0") + MONTH(DATEONLY([Start Date]@row)) + IF(DAY(DATEONLY([Start Date]@row)) < 10, "0") + DAY(DATEONLY([Start Date]@row)) + IF([Start Time]@row < 1000, "0") + [Start Time]@row), "")

End Date Time Helper

=IFERROR(VALUE(YEAR(DATEONLY([End Date]@row)) + "" + IF(MONTH(DATEONLY([End Date]@row)) < 10, "0") + MONTH(DATEONLY([End Date]@row)) + IF(DAY(DATEONLY([End Date]@row)) < 10, "0") + DAY(DATEONLY([End Date]@row)) + IF([End Time]@row < 1000, "0") + [End Time]@row), "")

Formula to get your Yes/No's

=IF(COUNTIFS(SAP:SAP, SAP@row, [End Date Time Helper]:[End Date Time Helper], @cell >= [Start Date Time Helper]@row, [Start Date Time Helper]:[Start Date Time Helper], @cell <= [End Date Time Helper]@row) > 1, "No", "Yes")

Michelle T

@Hollie Green That did the trick, thank you so much!!!