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

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

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. 

Hi, @kuovonne, Thank you very much for your reply!

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

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.

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. 

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.