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')
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')
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.
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.
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.
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.
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.
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.
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?
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!
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!
Thanks for pulling this together! Was exactly what I was looking for.