Aging Calculation

Question

Hi, I am trying to calculate Aging a item in sharepoint. there will be four columns such as [Approval Date], [Completion Date], [Query Raised Date] and [Query Resolved Date]. So the system should compare date total aging of instructions from Approval Date to Completion Date and exclude the Query Period (if Any Raised).

=DATEDIF([Approval Date],[Completion Date],"d")-DATEDIF([Query Raised Date],[QueryResolved Date ],"d"), this is the formula i used but this is throwing #NUM! error if any of the value is missing.

completion date may not be available for all the items and query fileds will be updated only if required and will be left blank.

can someone help me in getting the issue resolved!

Answer 1

Dear Bharathi,

Sorry for the late reply.

Based on my understanding, if some values missing such as Approval Date or Completion Date or only some values exist and miss another between Query Raised Date or Query Resolved Date, the calculation will be failed as there will be no age in calculation. In that situation, you may change it to No Age based on the original formula =DATEDIF([Approval Date],[Completion Date],"d")-DATEDIF([Query Raised Date],[QueryResolved Date ],"d") you use.

You may use the following formula to make the calculated column work.

Calculated Column: Age Calculate

Formula: =IF(OR(ApprovalDate=0,CompletionDate=0),"No Age",IF(AND(QueryRaisedDate=0,QueryResolvedDate=0),DATEDIF(ApprovalDate,CompletionDate,"d")-DATEDIF(QueryRaisedDate,QueryResolvedDate,"d"),IF(AND(AND(ApprovalDate<>0,CompletionDate<>0),AND(QueryRaisedDate<>0,QueryResolvedDate<>0)),DATEDIF(ApprovalDate,CompletionDate,"d")-DATEDIF(QueryRaisedDate,QueryResolvedDate,"d"),"No Age")))

Result:

If something is misunderstood, welcome to post your updates at your convenience and let us confirm the actual requirement in your environment.

Thanks for your effort and time!

Sincerely,

Cliff | Microsoft Community Moderator

Answer 2

That Resolved the issue, thanks a ton!

Answer 3

Dear Bharathi,

Welcome to the forum here.

I may need some time to do a test and I'll update here as soon as possible.

Thanks for your effort and time!

Sincerely,

Cliff | Microsoft Community Moderator