Hey, I am trying to create a feature in the application were a user can pick start date and end date from the Honeycode calendar but what I wanted is that when selecting the date it should exclude the Saturdays, Sundays and a list of holidays as well.
As you can see in the below Screenshot, one can select a start date and end date, and duration for their leave is auto-populated in No. of days section. Same start & end date means the user has taken 1 day leave.
So, I want to apply a formula on No.of days section(duration) which will exclude the Saturdays - Sundays and a list of holidays as well.
I came across the WEEKDAY() function but couldn't figure out how to use it?
I don't think it's possible to exclude days on that calendar picker, so I would suggest using the WEEKDAY() formula to display a warning message if a Saturday/Sunday is selected.
WEEKDAY() simply returns a number 1-7. Saturday is 7 and Sunday is 1. So, in a warning message you can set the visibility to =IF(OR(WEEKDAY($[Start])=1,WEEKDAY($[Start])=7,WEEKDAY($[Last Day])=1,WEEKDAY($[Last Day])=7),TRUE,FALSE)
For Holidays, I think you would create a Holidays table and add to that warning message if there's any of those dates selected.
Idk how, but I think it should be possible to exclude Saturdays and Sundays. Cause, I'm trying to create a Employee Leave Management application. So, if a employee requests a leave off, let's say from "Thursday to Monday".
The count of duration should be = 3(excluding Sat & Sun).
So, I want to exclude those weekends and if possible a list of holidays as well.
Let's Say, in the above example - Friday is a company holiday then the duration should be = 2(excluding friday, Saturday and Sunday as well).
The duration between two dates can be calculated using =DAYS Function. But how to exclude weekends and holidays????
I've created a table with list of holidays and also specified it with dates correspondingly.
But I'm not able to figure how to use this in the formula to achieve the desired result.
Any help on that?
Pardon the brevity & spell errors, sent from my mobile.
I was able to calculate the days since a day with this formula. It only counts Monday-Friday. I think you should be able to use it by replacing TODAY() with your variables and taking the difference between the 2.
