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

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.