Aging Formula?

Guaca Mohle

Looking for a formula that would express the following logic:

If item has been approved [Approval Date],

Count # of days from Approval Date to TODAY.

If item has been completed, [Completed Date],

Count # of days from Approval Date to Completed Date.

I've looked through the forums, but can't find something that appears similar.

Find more posts tagged with

Accepted answers

Mike Wilday

I would use a simple IF statement. Are you wanting to count the number of days for each in the same column? Or in different columns? If in the same column, then I would use this IF statement, assuming the completion would never pass before approval.

=IF(Isdate([Completed Date]@row), NETDAYS([Completed Date]@row, TODAY()), IF(Isdate([Approval Date]@row), NETDAYS([Approval Date]@row, TODAY()),"Not Approved")

IF you want to do business days instead of calendar days you can use NETWORKDAYS in place of NETDAYS.

I Hope that helps!

Guaca Mohle

Yes, it's Text/Number. I was FINALLY able to get it to work as I needed it to. Here's the final product:

One of the key gotchas was another close parenthesis needed after the second reference to [Actual Completed Date]@row.

=IF(ISDATE([Actual Completed Date]@row), NETWORKDAYS([Approver Date]@row, [Actual Completed Date]@row) - 1, IF(ISDATE([Approver Date]@row), NETWORKDAYS([Approver Date]@row, TODAY()) - 1, "Not Approved"))

Thank you for your help along the journey to this.

All comments

Mike Wilday

=IF(Isdate([Completed Date]@row), NETDAYS([Completed Date]@row, TODAY()), IF(Isdate([Approval Date]@row), NETDAYS([Approval Date]@row, TODAY()),"Not Approved")

IF you want to do business days instead of calendar days you can use NETWORKDAYS in place of NETDAYS.

I Hope that helps!

Guaca Mohle

Yes, I'll capture all results in a single column. Thank you! I'll give this a try.

Guaca Mohle

@mike Wilday I'm getting an #INVALID DATA TYPE error. Here's the formula as I trying to use it:

=IF(ISDATE([Actual Completed Date]@row), NETWORKDAYS([Actual Completed Date]@row, [Approver Date]@row, IF(ISDATE([Approver Date]@row), NETWORKDAYS([Approver Date]@row, TODAY()), "Not Approved")))

One change I made to your suggestion: if completed, it should count days between approval date and completed date. (No calculation to TODAY needed. -- Only if the item has not yet been completed am I interested in how many days from today have elapsed since it was approved.)

Mike Wilday

What type of column are you adding this data to? Is it a text/number column? Also, are you getting the error on rows where there is no date? Can you share a screenshot of the error in context and blur any sensitive data?

Guaca Mohle

Yes, it's Text/Number. I was FINALLY able to get it to work as I needed it to. Here's the final product:

One of the key gotchas was another close parenthesis needed after the second reference to [Actual Completed Date]@row.

Thank you for your help along the journey to this.

Mike Wilday

Good catch. I'm glad I could get you pointed in the right direction. Dang parenthesis.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.