unable to run logical function (OR, AND,..) in calculated column on sharepoint online

Copper Contributor

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

0 Replies