2

I am developing an automated calendar for a school on AirTable, but I am struggling to add if there is a bank holiday between the start date and end date. If the bank holiday falls on a weekday, it needs to add +1 day to the end date.

I have created a "bank holiday" table, as well as a full calendar table (containing everyday of the year and what weekday it falls on).

I'd like for the user to have the end date generated automatically by only writing the start date.

For example: I have a session starting on Monday, 7th of march. The "session type" would be "Course 1", which has a duration of 10 days (two weeks, monday to friday - I don't know if the duration should be 14 days instead?). If there's a bank holiday in the 10 days after the start date, and it's on a weekday, it needs to add +1 to the end date. If the end date falls on a sunday, it needs to add +2 so that it ends on a Monday. The course is divided in multiple classes, it would also be awesome to have a timeline view with the full Course and all the classes.

Here are some screenshots of my tables :

enter image description here

enter image description here

enter image description here

I know there is a possibility to write a script in Python with an AirTable API... but is there an easier way ?

grunenwaldmargaux
  • 25
  • 2

1 Answers1

0

I've dealt with a problem like this before: when an event happened in relation to other events mattered.

You'd think by the use of the words "in relation" that you could deal with this in a relational way, but I see it as a rule:

If Event A happens at Time 1, and it's so much or so little time from Event B, then do with Event A...

And, again by my way of thinking, rules are expressed in code.

And if you actually even can express that with a relation, I think it'd be very convoluted.

So, for your problem, you need to encode every schedule-able day that's 10 working days or less before a Bank Holiday (BH) so that if the day is selected you know it's less than 10 days from a BH and can conditionally add another day to the end date.

I looked at your examples, and here's my solution. I have a Calendar table which has all days, and two supporting "Bank Holiday" fields: does the date fall on a BH, and if not, is the date 10 days or less from a BH. We're looking at my All view, here:

enter image description here

I also have the view, Weekday, not bank holiday, and that's the view that you can pick a day from to schedule an event from the Event table.

The Event table:

enter image description here

You pick a day from the Start day field. Start day < 10 work days to Bank holiday? is a lookup field from the Calendar table for that day. End day is this formula:

DATETIME_FORMAT(
  DATEADD(
    {Start date}, 
    IF(
      {Start day < 10 work days to Bank holiday?}, 
      14, 
      13),
    'day'),
  'ddd, MMM Do')
Zach Young
  • 10,137
  • 4
  • 32
  • 53