Skip to main content

Shift schedule with IF-Formulas

  • June 26, 2019
  • 3 replies
  • 28 views
S

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?

3 replies

Kamille_Parks11
Forum|alt.badge.img+27

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.

S

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.


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)

S

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.


Main View - Airtable

Explore the "Main" view on Airtable.


Terms & ConditionsAccessibility statement