Scheduling Database - Availability using SUBSTITUTE()

#1

Hi, forumla gurus!

I’m working on a scheduling database linked to our list of work. I’m a little caught up in the process of flagging when a staff member is on two shifts at the same time.

Using @W_Vann_Hall’s formula to count occurrences in a string, using the start datetime and a list of all the staff member’s shift times from a rollup field, I can flag when they have a shift that starts at the same time, as below.

IF((LEN(ARRAYJOIN({All Allocated Start Times}))-LEN(SUBSTITUTE(ARRAYJOIN({All Allocated Start Times}),{Start Time},"")))/LEN({Start Time})>1,"Clashing",BLANK())

The next step is adding to the formula so that if the start time is between the start and end times of existing shifts, it raises the clashing flag.

Any help in the process would be appreciated!

0 Likes

#2

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:

16%20PM

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

#3

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.

0 Likes

#4

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

32%20AM

0 Likes

#5

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.

0 Likes

#6

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