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")
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!
Update: I was able to set up an automation via a different approach to work around this challenge. Thank you anywa!
