The Airtable Community will undergo scheduled maintenance on September 17 from 10:00 PM PST to 11:15 PM PST. During this period, you may experience temporary disruptions. We apologize for any inconvenience and appreciate your understanding.

How to check if a date is a weekend or a holiday?

Topic Labels: Dates & Timezones Formulas
37060 6
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

Hi expert guys and gals,

I am struggling with what appears to be a straightforward mundane issue, but I have scoured the Community and the internet without any luck.

I have workers schedules and sometimes they work Monday to Friday, but sometimes they work, say, every other day and some of their shifts fall during the weekend or during a national holiday. So I have a field with multiple select where it shows which days they (will) work and how many hours - from there multiply the number of days by the hours to get the total payable hours per week and consequently the total pay for the week.

How can I go about finding which of their shifts/dates that they have to work will fall during a weekend or a holiday, so I can pay them double on these dates.

The question is twofold, and the easy part is, I think I can use an IF formula to check if a specific date they submitted an hours report (AFTER completing the work) is a workday or not.

The harder part is when I am preparing the client offer BEFORE the month begins - how can I check which shifts will have to be double pay?

Any help is deeply appreciated. Thank you!

6 Replies 6
5 - Automation Enthusiast

Hey, i would also be super keen to find a workaround for this. Thanks

18 - Pluto

Do you have a date field? You can find out if a date is a weekend with the WEEKDAY() function. A result of 0 or 6 is a Sunday or Saturday.

You could also use DATETIME_FORMAT() to convert the date to a day of the week.

Date ranges are trickier. You might be able to do something with DATETIME_DIFF() versus WORKDAY_DIFF().

Holidays are even trickier because you need to have your own calendar holidays.

Yet another option is to use a linked table of dates, with one record per day. The date record could have a field that indicates if it is a holiday.

5 - Automation Enthusiast

And apologies for the delay on my part. I really appreciate your help, but have been thinking what is the optimal way to tackle this issue.

There is no problem with having a list of calendar holidays. I'll leave this part for last.

@kuovonne wrote:

Do you have a date field? You can find out if a date is a weekend with the WEEKDAY() function. A result of 0 or 6 is a Sunday or Saturday.

This is exactly what I had in mind for sorting the once-submitted reports. Easy-peasy.

@kuovonne wrote:

You could also use DATETIME_FORMAT() to convert the date to a day of the week.

Now this is what I am trying to tackle, but backwards. I have the preliminary working days of the week (as per the original screenshot) - a multiple select field.

Is there a way to grab each one of the selected options, convert the string to a weekday, then check in the upcoming month how many of each selected there are - how many Mondays there are, how many Wednesdays and so on, and to sum up those numbers?

Can you think of anything or am I overcomplicating things?

I am trying to make a very polished version of a time tracking tool for internal use 😐

Thank you again for your help!

~M

5 - Automation Enthusiast

I suppose I can use an IF formula, but not sure how to word it. Something of the sort:

if day is Weekday, +1 to weekdays, else, +1 to weekends
sum(weekdays + 2* weekends)

The multiplier (2*) is for the double pay on weekends.

But again, my problem is how to convert the multiple select weekday options to values for a formula.

18 - Pluto

I don’t have a good feel for what you are trying to accomplish, but maybe some of these ideas will help.

If you have a starting date where you know the day of the week, such as a Sunday, you can use DATEADD() to get a future day of the week. Use SWITCH() to convert a single day of the week to a number. But this won’t work if the days of the week are all in a multiple select field because you won’t have a single day of the week.

If you want to find out how many Mondays are in a given span of dates, that is possible, but a rather complex formula that I cannot write right now on my phone.

5 - Automation Enthusiast

Bringing this thread up as I am also struggling to build a functional shift scheduler in AT and was hoping someone in the community has experience building similar bases before.