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

%3CLINGO-SUB%20id%3D%22lingo-sub-2734932%22%20slang%3D%22en-US%22%3Eunable%20to%20run%20logical%20function%20(OR%2C%20AND%2C..)%20in%20calculated%20column%20on%20sharepoint%20online%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2734932%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20tracking%20leave%20request%20of%20employee%20by%20utilizing%20sharepoint%20online.%20I%20need%20to%20create%20calculated%20column%20that%20can%20let%20me%20know%20the%20%22total%20leave%20days%22%20of%20employees%20%3CSTRONG%3Eexcluding%20weekend%20(Saturday%20and%20Sunday)%20and%20holidays.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDuring%20my%20search%2C%20i%20got%20this%20solution%20from%20this%20thread%20%22%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fsharepoint%2Fcalculate-working-days-in-sharepoint%2Fm-p%2F1081664%26quot%3B%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fsharepoint%2Fcalculate-working-days-in-sharepoint%2Fm-p%2F1081664%22%3C%2FA%3E.%20But%20there%20are%202%20things%20arised%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E1.About%20the%20formula%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20testing%20it%20by%20running%20it%20in%20excel%2C%20it%20worked%20very%20well.%20But%20when%20i%20put%20it%20in%20sharepoint%20calculated%20column%2C%20it%20dit%20not.%20Then%20i%20found%20out%20that%20is%20because%20calculated%20column%20failed%20to%20run%20%3CSTRONG%3EOR%20function%20(part%20of%20entire%20formula)%2C%20%3C%2FSTRONG%3Ethis%20is%20my%20formula%20(edited%20from%20from%20link%20above%20to%20match%20my%20case)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(DATEDIF(%5BStart%20Of%20Leave%5D%2C%5BEnd%20Of%20Leave%5D%2C%22D%22))-INT(DATEDIF(%5BStart%20Of%20Leave%5D%2C%5BEnd%20Of%20Leave%5D%2C%22D%22)%2F7)*2-IF(WEEKDAY(%5BEnd%20Of%20Leave%5D)%3CWEEKDAY%3E%3C%2FWEEKDAY%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22yulaw90_1-1631160412114.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F309162iA442FF45FB2DAE82%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22yulaw90_1-1631160412114.png%22%20alt%3D%22yulaw90_1-1631160412114.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22yulaw90_2-1631160452899.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F309163i4AF9C97477BD7D81%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22yulaw90_2-1631160452899.png%22%20alt%3D%22yulaw90_2-1631160452899.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E2.%20This%20formula%20can%20only%20solve%20excluding%20weekend%20but%20how%20to%20exclude%20also%20holidays%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2734932%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New 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

 

yulaw90_1-1631160412114.png

 

yulaw90_2-1631160452899.png

 

2. This formula can only solve excluding weekend but how to exclude also holidays?

 

Thank you so much

0 Replies