Solved

Identify if ANY dates (in a string of dates) show up in another string of dates

Forum|Forum|2 years ago
August 24, 2023
3 replies
43 views

Lisa_Bauer
Inspiring

TLDR: I'm trying to mark employees as "OOO" if they are OOO for 3 or more days of the week (vs if they are only OOO for 1-2 days of the week they still show up as available).

I have a system set up where I have created an "open space" record for every week and previously I had it set up so if the employee was OOO for any part of the week (even if it was just one day) the open space record will show up in red as "unavailable". But I want to adjust that to only showing up as unavailable if they are gone for more than 3 days of the week.

I have created fields in my table that shows all the days of each record, so for the Open Space records it lists out all 5 days of the week vs for any OOO records it lists out all of the OOO days.

I'm looking for a formula that compares the two. When the person is only OOO for one day, the formula below works great (as you see for my 12/1/2023 example)

IF(FIND({[Prototype] Daily Dates 🧮 🏳️}, {[Prototype] Daily OOO 🧮 🏳️}&""), "OOO", "Open Space")

but once the OOO dates turn into a string of dates, unless EVERY date is in the open space string of dates the formula won't work.

Is there a "If(Find()" formula where it looks for ANY overlap rather than complete?

Thank you for your help!

Best answer by Lisa_Bauer

Update: I was able to set up an automation via a different approach to work around this challenge. Thank you anywa!

+22

Russell_Bishop1
Inspiring
Forum|Forum|2 years ago
August 24, 2023

Hey @Lisa_Bauer , could you explain a little more about the structure of the data?

Table 1 (Staff holiday bookings)
Start date, Staff member
Table 2 (Available slots)

Something like this so that I can provide some help?

It sounds like the tables you need are:
Holiday bookings (staff, start date, end date, # days booked (computed)
Staff members
Staff weeks (1 record for each week, each staff member)

When a new holiday booking is made, it links to the correct Staff weeks record, and that staff week record uses a ROLLUP() to count the total days booked that week.

Use a formula field for that Staff week to say IF({# days holiday} >= 3, "OOO", "Open space")

Lisa_Bauer
Author
Inspiring
Forum|Forum|2 years ago
August 24, 2023

Hi @Russell_Bishop1 ! Thanks for responding. My tables are a bit complex so I'll do my best to explain.

I have 2 main tables that are involved in this scenario:

Table 1: Employee Table
Table 2: Resourcing Table

In the resourcing table, this is where I have both the records for any OOO with its start and end date and all of the open space records for each week of the year that changes its color based on if an employee is available or not.

And here are all the relevant fields that I am using for this problem:

Employee Table:

Roll up field that strings all the OOO dates for the selected employee from the Resourcing Table

Resourcing Table:

A formula field that lists out all the dates within the selected start and end date. For the OOO records, this shows all the OOO days. For the open space records, it shows the Monday-Friday days of each week. (fdormula below:)

REGEX_REPLACE(CONCATENATE(
  DATETIME_FORMAT(
    {Start Date 📅}, 
    "M/D/YY"
  ) & ", ",
  IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 1,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 1, 'day'),
        "M/D/YY"
    ) & ", "
  ),
  IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 2,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 2, 'day'),
        "M/D/YY"
    ) & ", "
  ),
  IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 3,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 3, 'day'),
        "M/D/YY"
    ) & ", "
  ),
  IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 4,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 4, 'day'),
        "M/D/YY"
    ) & ", "
  ),
  IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 5,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 5, 'day'),
        "M/D/YY"
    ) & ", "
  ),
  IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 6,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 6, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 7,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 7, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 8,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 8, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 9,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 9, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 10,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 10, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 11,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 11, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 12,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 12, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 13,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 13, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 14,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 14, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 15,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 15, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 16,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 16, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 17,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 17, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 18,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 18, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 19,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 19, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 20,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 20, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 21,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 21, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 22,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 22, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 23,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 23, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 24,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 24, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 25,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 25, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 26,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 26, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 27,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 27, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 28,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 28, 'day'),
        "M/D/YY"
    ) & ", "
  ),IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 29,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 29, 'day'),
        "M/D/YY"
    ) & ", "
  ),
  IF(
    DATETIME_DIFF({End Date 📅}, {Start Date 📅}, 'days') >= 30,
    DATETIME_FORMAT(
        DATEADD({Start Date 📅}, 30, 'day'),
        "M/D/YY"
    ) & ", "
  )
), ", $", "")

Look up field of the Roll up field from the Employee Table (above)
Formula field that I was hoping would let me know if any of the OOO dates conflicts with the open space dates

IF(FIND({[Prototype] Daily Dates 🧮 🏳️}, {[Prototype] Daily OOO 🧮 🏳️}&""), "OOO", "Open Space")

The reason why something like the formula you suggested ( IF({# days holiday} >= 3, "OOO", "Open space")) won't work is because it's not considering if it's more than 3 days within the week. For example, if an employee starts their OOO on a Friday for 2 weeks, it fits under the "more than 3 days" situation of your suggested formula, but what for what we are hoping to use it for, we would want the first week to still show up as "open space" because the employee is only missing one day (Friday).

Let me know if this is enough information!

Lisa_Bauer
Author
Inspiring
Answer
Forum|Forum|2 years ago
August 28, 2023

Update: I was able to set up an automation via a different approach to work around this challenge. Thank you anywa!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded