Scheduling Database - Availability using SUBSTITUTE()

The solution below will only work when creating a new shift record for a given staff member, and comparing that shift’s start time against the rest for that staff member, which is what it sounds like you want. The other caveat is that it won’t (and can’t) put the clashing indicator on the new record, but rather on any shift record that it clashes with. Here’s how it looks in my test:

The latest shift start time for Pete falls in the middle of his first shift, so that first shift is tagged as clashing.

First off, to make this a bit easier, I made a field called {Last Start} that extracts the last start time—the one you’re currently adding—from the contents of {All Allocated Start Times}. This needs to be done twice in the main formula, so rather than put the bulky code in there twice, I made the {Last Start} field to act as a variable of sorts. Here’s the formula for that field (which you can hide once it’s all working):

DATETIME_PARSE(RIGHT({All Allocated Start Times} & "", LEN({Start Time})), "YYYY-MM-DDTHH:mm:ss.SSSZ")

And here’s my modified version of your formula to add the new check:

IF(
    OR(
        (
            LEN(
                ARRAYJOIN(
                    {All Allocated Start Times}
                )
            )-LEN(
                SUBSTITUTE(
                    ARRAYJOIN(
                        {All Allocated Start Times}
                    ),
                    {Start Time},
                    ""
                )
            )
        )/LEN(
            {Start Time}
        ) > 1,
        IF(
            AND(
                {Start Time},
                {End Time}
            ),
            AND(
                IS_AFTER(
                    {Last Start},
                    {Start Time}
                ),
                IS_BEFORE(
                    {Last Start},
                    {End Time}
                )
            )
        )
    ),
    "Clashing"
)

IF(
    OR(
        (
            LEN(
                ARRAYJOIN(
                    {All Allocated Start Times}
                )
            )-LEN(
                SUBSTITUTE(
                    ARRAYJOIN(
                        {All Allocated Start Times}
                    ),
                    {Start Time},
                    ""
                )
            )
        )/LEN(
            {Start Time}
        ) > 1,
        IF(
            AND(
                {Start Time},
                {End Time}
            ),
            AND(
                IS_AFTER(
                    {Last Start},
                    {Start Time}
                ),
                IS_BEFORE(
                    {Last Start},
                    {End Time}
                )
            )
        )
    ),
    "Clashing"
)

Thanks, Justin, this is awesome!

I’ve put this in my base and had a play around with it - it appears to work well when allocating shifts in chronological order.

However, if we’re putting someone on a shift before the time they’re already working and the end time causes the clash, it doesn’t flag it - which I know is different to what I was originally looking for.

I’ll keep working on it and see if I can incorporate this.

Duplicate {Last Start}, rename it to {Last End}, and use this formula:

DATETIME_PARSE(RIGHT({All Allocated End Times} & "", LEN({End Time})), "YYYY-MM-DDTHH:mm:ss.SSSZ")

With that done, this is the new primary formula, which will flag as clashing if either a start or end time for the last-entered shift falls between any other shift’s start/end combination.

IF(
    OR(
        (
            LEN(
                ARRAYJOIN(
                    {All Allocated Start Times}
                )
            )-LEN(
                SUBSTITUTE(
                    ARRAYJOIN(
                        {All Allocated Start Times}
                    ),
                    {Start Time},
                    ""
                )
            )
        )/LEN(
            {Start Time}
        ) > 1,
        IF(
            AND(
                {Start Time},
                {End Time}
            ),
            OR(
                AND(
                    IS_AFTER(
                        {Last Start},
                        {Start Time}
                    ),
                    IS_BEFORE(
                        {Last Start},
                        {End Time}
                    )
                ), AND(
                    IS_AFTER(
                        {Last End},
                        {Start Time}
                    ),
                    IS_BEFORE(
                        {Last End},
                        {End Time}
                    )
                )
            )
        )
    ),
    "Clashing"
)

The order we assign the shifts seems to be primarily important here.

If we schedule chronologically, Tom’s 10:30 shift causes a clash with his 9:00, as it should, since the both the start and end time are within the earlier shift.

If we schedule him on the 10:30 and then the 9:00 (or even replace him on the clashing shift with himself), it won’t flag the clash, since neither the start nor end time are within the 10:30 shift, but the time inbetween is.

All I can think of is to add some conditions to the formula to check if other times between the start and end cause clashes too, but I’m hoping there’s some hidden logic to this puzzle that that will avoid having to brute force the IF formulas.

Currently the system only looks for either the {Last Start} or {Last End} to fall between a given record’s {Shift Start Time} or {Shift End Time}. Situations like you demonstrated where the {Last Start} and {Last End} surround a given shift’s start/end require a different comparison. Here’s an updated formula:

IF(
    OR(
        (
            LEN(
                ARRAYJOIN(
                    {All Allocated Start Times}
                )
            )-LEN(
                SUBSTITUTE(
                    ARRAYJOIN(
                        {All Allocated Start Times}
                    ),
                    {Shift Start Time},
                    ""
                )
            )
        )/LEN(
            {Shift Start Time}
        ) > 1,
        IF(
            AND(
                {Shift Start Time},
                {Shift End Time}
            ),
            OR(
                AND(
                    IS_AFTER(
                        {Last Start},
                        {Shift Start Time}
                    ),
                    IS_BEFORE(
                        {Last Start},
                        {Shift End Time}
                    )
                ), AND(
                    IS_AFTER(
                        {Last End},
                        {Shift Start Time}
                    ),
                    IS_BEFORE(
                        {Last End},
                        {Shift End Time}
                    )
                ), AND(
                    IS_AFTER(
                        {Shift Start Time},
                        {Last Start}
                    ),
                    IS_BEFORE(
                        {Shift Start Time},
                        {Last End}
                    )
                ), AND(
                    IS_AFTER(
                        {Shift End Time},
                        {Last Start}
                    ),
                    IS_BEFORE(
                        {Shift End Time},
                        {Last End}
                    )
                )
            )
        )
    ),
    "Clashing"
)

Hi there! I’m trying to replicate this, as I am trying to make it so the formula notifies me when a presenter has been scheduled to give a presentation at the same time as they have already been scheduled. However, I’ve very new to airtable and formulas such as these. I’ve entered in the formulas above and tried to repeat what’s already been suggested, but my “Clash” field isn’t flagging at all. I don’t think I did the “All Allocated Start Times” field correctly. Can someone explain how I set that up? The other fields were fairly well discussed. All I have in reference to the “All Allocated Start Times” field, though, is OP’s brief mention of “a list of all the staff member’s shift times from a rollup field”.

Can someone explain how I would set that up to make the other formulas work properly? Again - super new to Airtable and formulas, so thanks for your patience and help!

@Heather_Kravagna Sorry that I didn’t respond sooner. Were you able to get this figured out, or do you still need help?

I'm pretty sure what you were doing here is exactly what I need but it feels over my head. 

I own and operate a cleaning business. I have created a base that lists all the locations and the scheduled dates and times. I also created fields that show the day of the week by both number 0-6 or the actual name Monday, Tues, etc. I recreated the Employee scheduling base within my base so I could easily relate bookings with cleaners. So the employee table shows what days of the week the cleaner is available. I created a field on the booking table to be able to select cleaners to assign to the job and I included a lookup field to be able to compare the available days to the service day. So far the availability is only by day not by specific time ranges but if I need to expand it for days and times I can look into that.

 Is there a way for me to be able to have the program give me a list of cleaners that are available that day of the week and not scheduled on another booking? Or have you only gotten it to a point where it will tell you they are already scheduled when you schedule them?