Power Platform Community Forum Thread Details

Hi,

I am looking for ways to update the ageing column in my Microsoft List.

Ideally, I am looking for alternative options to the below formula of my column > auto calculated:

=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"

Based on the formula above, I want to exclude weekends on the calculation of my ageing because the value will be compared to FA priority age (meaning allowable number of days before system will tag this as "Overdue"). If Query age > FA priority value then set Query alert to "Overdue".

This is a vital setting since all "Overdue" items will be sent as list via email using Power Automate as well.

My question would be if is there a way to use NETWORKDAYS formula in Microsoft List or should I go to Power Automate and use Update Items flow.

I would like to ask if you can share both steps so I can replicate?

Categories:

Building Power Apps

Found a formula here: 2013 - Calculate number of working days between two dates - SharePoint Stack Exchange

Formula:

=IF(Status="Open",(IF(ISERROR(DATEDIF([Raised date],[Last item refresh],"d")),"",(DATEDIF([Raised date],[Last item refresh],"d"))+1-INT(DATEDIF([Raised date],[Last item refresh],"d")/7)*2-IF((WEEKDAY([Last item refresh])-WEEKDAY([Raised date]))<0,2,0)-IF(OR(AND(WEEKDAY([Last item refresh])=7,WEEKDAY([Raised date])=7),AND(WEEKDAY([Last item refresh])=1,WEEKDAY([Raised date])=1)),1,0)-IF(AND(WEEKDAY([Raised date])=1,(WEEKDAY([Last item refresh])-WEEKDAY([Raised date]))>0),1,0)-IF(AND(NOT(WEEKDAY([Raised date])=7),WEEKDAY([Last item refresh])=7),1,0))),(IF(ISERROR(DATEDIF([Raised date],[Query resolution date],"d")),"",(DATEDIF([Raised date],[Query resolution date],"d"))+1-INT(DATEDIF([Raised date],[Query resolution date],"d")/7)*2-IF((WEEKDAY([Query resolution date])-WEEKDAY([Raised date]))<0,2,0)-IF(OR(AND(WEEKDAY([Query resolution date])=7,WEEKDAY([Raised date])=7),AND(WEEKDAY([Query resolution date])=1,WEEKDAY([Raised date])=1)),1,0)-IF(AND(WEEKDAY([Raised date])=1,(WEEKDAY([Query resolution date])-WEEKDAY([Raised date]))>0),1,0)-IF(AND(NOT(WEEKDAY([Raised date])=7),WEEKDAY([Query resolution date])=7),1,0))))