Aging Formula?
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.
Best Answers
-
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!
-
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.
Answers
-
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!
-
Yes, I'll capture all results in a single column. Thank you! I'll give this a try.
-
@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.)
-
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?
-
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.
-
Good catch. I'm glad I could get you pointed in the right direction. Dang parenthesis.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!