Shift schedule with IF-Formulas

Stad_Kystbygg_A · ‎Jun 26, 2019

Hello!

I try to set up a work schedule with different schedule like 7-7, 5-2 and 14-14 (days on -off)
This is calculated from a start date.
However I have some issues.
I have set up a 52 + 52 formulas for calculating on-off shift date based on a startdate,
then I want the on-of dates to adjust for vacation dates.

The first formulas:

IF({Startdate on #1} = BLANK(), BLANK(),
IF({Shift} = ‘14-14’, DATEADD({Startdate on #1}, 13,‘day’),
IF({Shift} = ‘7-7’, DATEADD({Startdate on #1}, 6,‘day’),
IF({Shift} = ‘5-2’, DATEADD({Startdate on #1}, 4,‘day’,
AND(OR(IS_BEFORE({vacation 1 to},{vacation 1 to}))))))))

The second:
IF({Shift} = ‘14-14’, DATEADD({Startdate on #1}, 28,‘day’),
IF({Shift} = ‘7-7’, DATEADD({Startdate on #1}, 14,‘day’),
IF({Shift} = ‘5-2’, DATEADD({Startdate on #1}, 7,‘day’,
IF({Startdate on #1} = BLANK(), BLANK(),
AND(OR(IS_AFTER({vacation 1 from},{vacation 1 from}))))))))

I cant get the ON-OFF date to adust for vacation.

And a second issue is that the calender can only handel 50+50 dates.

Annyone have a better way doing this?

Kamille_Parks · ‎Jun 26, 2019

That piece of the formula is asking “Is Date A after Date A?” Shouldn’t you be referencing two different date fields instead of just one?

Correct me if I’m wrong:
You’re trying to calculate the end date of someone’s shift. Shifts are consecutive blocks of days unless they have a vacation in the middle. The base should suspend the count of consecutive shift days during the vacation.

Stad_Kystbygg_A · ‎Jun 30, 2019

Hello Kamille!

That is correct! The base should suspend the count of consecutive shift days during the vacation.

Im new to Airtable the formulas and are little confused here if and when I need to use AND-OR-IF, and
If ‘vacation 1 from’ is after the schadule date it need to show vacation 1 from (date)

Stad_Kystbygg_A · ‎Jun 30, 2019

Main View - Airtable

Explore the "Main" view on Airtable.