Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: Dates & Timezones Formulas
51610 6
cancel
Showing results for 
Search instead for 
Did you mean: 
hristovm
5 - Automation Enthusiast
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. 

hristovm_0-1685143068898.png

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
Philo
5 - Automation Enthusiast
5 - Automation Enthusiast

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. 

hristovm
5 - Automation Enthusiast
5 - Automation Enthusiast

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
5 - Automation Enthusiast
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.

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
5 - Automation Enthusiast
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.