Computing ageing based on 2 columns and exclude weekends

[ComicSansRival]

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",[Last item refresh]-[Raised date],[Query resolution date]-[Raised date])

Status - pertains to the status of a query

Last item refresh - this is also calculated via Power Automate wherein it returns the value of the date today (date when the last refresh happen)

Raised date - date when the query was created

Query resolution date - when the query was set to "Closed"

I want to exclude the weekends on ageing calculation since we are following business days and another Power Automate is dependent to this wherein there is an automated email that will be forwarded to users if certain queries are overdue.

So I am planning to incorporate NETWORKDAYS in the formula but I am not sure how to build this in Microsoft List.

[#2]

@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)