Forum Discussion
Computing ageing based on 2 columns and exclude weekends
Hi, I am currently building a list wherein one column is calculated and expected result is to show the number of days or age of a query. Here is the current formula: =IF(Status="Open",[La...
ComicSansRival NETWORKDAYS is an Excel function, for SharePoint you need to use a combination of WEEKDAY and DATEDIF. The following will calculate the number of working days between the Start column and the End column:
=IF(ISERROR(DATEDIF(Start,End,"d")),"",(DATEDIF(Start,End,"d"))+1-INT(DATEDIF(Start,End,"d")/7)*2-IF((WEEKDAY(End)-WEEKDAY(Start))<0,2,0)-IF(OR(AND(WEEKDAY(End)=7,WEEKDAY(Start)=7),AND(WEEKDAY(End)=1,WEEKDAY(Start)=1)),1,0)-IF(AND(WEEKDAY(Start)=1,(WEEKDAY(End)-WEEKDAY(Start))>0),1,0)-IF(AND(NOT(WEEKDAY(Start)=7),WEEKDAY(End)=7),1,0))
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)