Aging Calculation based on Condition

[Rajender Singh]

Hi All,

I have created a SharePoint list where I want to calculate the aging of tickets in days based on Status of ticket. I have created the custom formula as well for this but somehow its how showing correct result special when calculation is related to Today() date. Below formula I am using it.

Can anybody suggest what is wrong in the formula. 

=IF(Status="Completed",DATEDIF(Created,Modified,"d"),
IF(Status="Cancelled by PMO",DATEDIF(Created,Modified,"d"),
IF(Status="Open",DATEDIF(Created,TODAY(),"d"),
IF(Status="Pending approval",DATEDIF(Created,TODAY(),"d"),
IF(Status="Rejected by FG",DATEDIF(Created,Modified,"d"),DATEDIF(Created,Modified,"d"))))))

[#2]

@Rajender Singh Your formula syntax seems fine to me. Try using NOW() instead of TODAY() once.

Also, you can try using Text function instead of DATEDIF function like: 

=TEXT([Column2]-[Column1],"dd")

Note: Calculated column values will be updated only when item is created/modified or when calculated formula itself is updated.

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

[#3]

@ganeshsanap 

Thank you for your comments. Is there any way using flow or other option so that calculated filed automatically update as compare to current date and until status changed to "completed" ?