I’ve got a table that pulls Google Calendar events for my team into Airtable. It’s there to build an interface that breaks the events down and gives summaries as to how many days are spent On Site, Working from Home, in the Office etc…
I need to find a way to be able to filter out, or mark with a checkbox, the records that happen on, or span a weekend. Google Calendar only brings in start and end dates of events.
Any pointers would be fantastic. I’m quite an experienced Airtable user, but have no experience of coding but willing to try.
This formula would be a good starting point, but it would need to be expanded in order to tell him if any of the dates in between the start date & end date also fall on a weekend.
I’m sure there’s some easy formula to pull this off that involves calculating the total number of days between the start date & the end date, and then subtracting the total number of days from the end date, but I’m not able to dedicate time right now to figuring out this exact formula.
Welcome to the Airtable Community!
IF( ( WORKDAY_DIFF(startDate, endDate) != DATETIME_DIFF(endDate, startDate, 'days') + 1 ), "includes weekend" )
Items to note:
DATETIME_DIFF take their inputs in different orders. Thus the dates are flip-flopped for the two functions.
WORKDAY_DIFF includes both the start and end date in its count, but
DATETIME_DIFF does not. For example, if both dates are the same workday,
WORKDAY_DIFF will give you 1, but
DATETIME_DIFF will give you 0. To account for this difference, I add 1 to the
If the dates have times associated with them (even hidden times), this might not work
If either date is missing, this will give an error
You might run into timezone issues.
Oh yes, I did miss that! The forum notification system just jumped me to your most recent comment. Thanks for posting that great formula! Very simple solution! :grinning_face_with_big_eyes: :raised_hands:
This is amazing, thank you so much everyone.
Just putting this through it’s paces, and I’ve now managed to get the formula to work with a bit of tweaking in Google Calendar, but now facing 2 new challenges.
Quick update on this;
I’ve worked around it with this formula for Saturday:
And this formula for Sunday:
Then I had another simple SUM formula field to add them to give me the number of weekend days worked in the event’s time period.
Credit to another user who’s post I now cannot find, but if I do I’ll link it here.
Thanks for the help everyone!