Want to flag overlapping dates AND times

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!

Find more posts tagged with

Formulas

Accepted answers

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")

All comments

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

Start Date Time Helper

End Date Time Helper

Formula to get your Yes/No's

Michelle T

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.