Sep 08 2021 09:18 PM
Hello Everyone,
I am tracking leave request of employee by utilizing sharepoint online. I need to create calculated column that can let me know the "total leave days" of employees excluding weekend (Saturday and Sunday) and holidays.
During my search, i got this solution from this thread "https://techcommunity.microsoft.com/t5/sharepoint/calculate-working-days-in-sharepoint/m-p/1081664". But there are 2 things arised:
1.About the formula:
I was testing it by running it in excel, it worked very well. But when i put it in sharepoint calculated column, it dit not. Then i found out that is because calculated column failed to run OR function (part of entire formula), this is my formula (edited from from link above to match my case):
(DATEDIF([Start Of Leave],[End Of Leave],"D"))-INT(DATEDIF([Start Of Leave],[End Of Leave],"D")/7)*2-IF(WEEKDAY([End Of Leave])<WEEKDAY([Start Of Leave]),2,IF(OR(WEEKDAY([End Of Leave])=7,WEEKDAY([Start Of Leave])=1),1,0))+1
2. This formula can only solve excluding weekend but how to exclude also holidays?
Thank you so much