Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Update Ageing using Power Automate

(0) ShareShare
ReportReport
Posted on by 133

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?

  • Verified answer
    ComicSansrival Profile Picture
    ComicSansrival 133 on at
    Re: Update Ageing using Power Automate

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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,475

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,767

Leaderboard