1

Hi I have a calculated column that counts me no. of weekdays

=(DATEDIF([Registration],[Gate 0],"D"))-INT(DATEDIF([Registration],[Gate 0],"D")/7)*2-IF((WEEKDAY([Gate 0])-WEEKDAY([Registration]))<0,2,0)+1

HOWEVER, a Gate 0 date may not be filled (as the gate 0 has not been reached yet), and I get a NUM! or Name? error. I NEED NUM to be blank or 0 in Reg-G0 calculated column. I have been trying lots of code with no success.

Example data

Project ID Registration Gate 0 Reg-G0
13234 19/02/2022 30/10/2022 180
44954 18/05/2022 #NUM!
69543 14/03/2021 23/05/2021 50
56456 25/03/2021 25/03/2023 522
44368 14/03/2021 14/06/2025 1110

I NEED NUM to be blank or 0.

1
  • Hi @Andrew, does my answer below help you in any way? If yes, please accept it as an Answer as it helped you & it will help others with similar question in future to find the correct answer easily. It also removes this question from "Unanswered questions" list. Oct 13, 2022 at 11:18

1 Answer 1

0

Try using this formula:

=IF(ISBLANK([Gate 0]),0,(DATEDIF([Registration],[Gate 0],"D"))-INT(DATEDIF([Registration],[Gate 0],"D")/7)*2-IF((WEEKDAY([Gate 0])-WEEKDAY([Registration]))<0,2,0)+1)

It will return 0 if [Gate 0] column is empty.

Documentation: SharePoint calculated column functions

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.