Check if record is on, or spans a weekend

Hi @Max_Spielbichler
Here is a formula that will tell you if a date in on a weekend or a weekday

IF(OR(WEEKDAY(Date)=0,WEEKDAY(Date)=6), 'Weekend', 'Weekday')

IF(OR(WEEKDAY(Date)=0,WEEKDAY(Date)=6), 'Weekend', 'Weekday')

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.

@ScottWorld
Good point. I can try to expand on it if this is not enough. I’m guessing you would need to get the DATETAIME_DIFF for the range, then WORKDAY() to find the weekends.

Welcome to the Airtable Community!

Try this:

IF(
   (
        WORKDAY_DIFF(startDate, endDate)
        != 
        DATETIME_DIFF(endDate, startDate, 'days') + 1
    ),
  "includes weekend"
)

Items to note:

• WORKDAY_DIFF and 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 DATETIME_DIFF.

• 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.

By itself, this will not work. If the total number of days is 6 or more more, you know there is a weekend. If you have fewer days, you need to take into account the starting day of the week, which is a pain.

Yep, exactly. This is why it would be a much more complex formula — although I don’t have time to wrap my brain around it at the moment. :stuck_out_tongue_winking_eye: Although I’m 99.9% sure that this can be done without scripting.

Maybe you missed my previous post that has the simpler formula.

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.

• This formula will tell you if there is a weekend included, but is there a way for it to say if it’s one or both days of the weekend?
• For really long events (spanning multiple weeks), is it possible to count the number of weekend days the event spans? Ie, if I’m away for work abroad for 4 weeks including their weekends, is there a way Airtable can tell me that I’ve accrued 8 days of TOIL?

Quick update on this;

I’ve worked around it with this formula for Saturday:

ROUNDDOWN((1+(DATETIME_DIFF({End},{Start},'days')-(6-WEEKDAY({Start})))/7),0)

And this formula for Sunday:

ROUNDDOWN((1+(DATETIME_DIFF({End},{Start},'days')-(5-WEEKDAY({Start})))/7),0)

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!

ROUNDDOWN((1+(DATETIME_DIFF({End},{Start},'days')-(6-WEEKDAY({Start})))/7),0)

ROUNDDOWN((1+(DATETIME_DIFF({End},{Start},'days')-(5-WEEKDAY({Start})))/7),0)

Thanks for pulling this together! Was exactly what I was looking for.