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

[hristovm]

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! 

[Philo]

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

[kuovonne]

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. 

[hristovm]

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

[hristovm]

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.

[kuovonne]

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. 

[Philo]

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.