Calculating an Aging Open Item

E. Chang

Hello, I am looking for a formula that would count the aging item that has any of the status (In Progress, Open, or Deferred).

If item has been "In Progress", "Open", or "Deferred" [Status], Count # of days from [Initiated Date] to TODAY.

If item has been Completed, it should not count the aging days and will return a value of zero since it is no longer an open aging record.

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

Paul Newcome

Try something like this...

=IF(Status@row <> "Completed", TODAY() - [Initiated Date]@row, 0)

SteCoxy

Relating to this, I've got 2x similar queries..

I've used the formula above @Paul Newcome to tell me how long since the creation date the submission has been in the process for. It would be good if the duration could stop counting when either an"Evidence Approved / Certified" or a "Certification Unsuccessful" status was selected - rather than it producing a 0. Is this possible? This would give us an indicator on how long each submission takes.

The other query is I would like a formula that provides an up to date duration of how long it has been at that status for.

I've got a number of helper columns that use the Record a Date automation to populate the cells when the according status has been selected. We've then got a formula that works out how long it takes between each status. That's great for a retrospective (to see how long it takes between each status in the process), but it doesn't advise us on the here and now.

Would it be possible to have 1 formula (thinking it might use the IF function) that refers to the Status column - and the date in these helper columns below - to advise how long it has been in that current status? Ideally, it would be great to have it in the one column as a "Current Status Duration". Obviously, we wouldn't want the formula to keep counting the days indefinitely, so we'd want it to stop counting when it reaches a status of either "Evidence Approved / Certified" or "Certification Unsuccessful".

Any guidance on this would be much appreciated!

Paul Newcome

@SteCoxy Yes. You would use an IF statement to output either TODAY or the date you want it to stop counting at.

=IF(Status@row <> "Completed", IF([Date Column]@row <> "", [Date Column]@row, TODAY()) - [Initiated Date]@row)

If the [Date Column is being driven by when the status does change to "Completed", you would use this instead:

=IF(Status@row <> "Completed", TODAY(), [Date Completed]@row) - [Initiated Date]@row

How long it has been in the current status would be the same idea.

SteCoxy

Hi Paul thank you for replying here.

Not sure if this complicates matters, but I need it to stop counting when it reaches 1 of 2 statuses and their according dates that are autopopulated by the Record a Date automation (I think it would be these that I'd need ot reference?). How would I ensure both outcomes are factored in? Is it through using OR?

The statuses are:

"Evidence Approved / Certified"

"Certification Unsuccessful" 

The columns where the dates get autopopulated are called:

[Evidence Approved / Certified (Date)]

[Certification Unsuccessful (Date)]

Also, can this sort of formula work if there's no dates in the columns that are being referenced?

SteCoxy

Just to let you know, I've got it to work in the Overall Duration column (where the Status is Evidence Approved / Certified and there's a date in the in the Evidence Approved / Certified (Date) column) using the following formula:

=IF(Status@row <> "Evidence Approved / Certified", TODAY(), [Evidence Approved / Certified (Date)]@row - Created@row)

However, for any rows where there's no date in the Evidence Approved / Certified (Date) column, it's got a INVALID COLUMN VALUE error message displaying.

Paul Newcome

You would use an OR. It would be similar logic to what I outlined above where we use an IF statement to output the "Ending Date" whether that be TODAY or a cell reference and then subtract the "starting date" from it.

In regards to your second piece, if the status is "Evidence Approved / Certified", shouldn't here be a date in the [Evidence Approved / Certified (Date)] column?

SteCoxy

There will be a date in the "Evidence Approved / Certified" or potentially the "Certification Unsuccessful" columns at some point, but it has to go through a number of statuses first as part of the workflow. The date columns ony get populated (by the Record a Date automation) when the row changes to the next status point as it's working its way through the process/workflow. They are blank until then. Will the formulas not work without a date in them? Is there a way around it if so?

I've tried the following on the Overall Duration column:

=IF(Status@row <> "Evidence Approved / Certified", TODAY(), [Evidence Approved / Certified (Date)]@row - Created@row), OR(Status@row <> "Certification Unsuccessful", TODAY(), [Certification Unsuccessful (Date)]@row - Created@row)) but I'm getting:

Paul Newcome

I'm not sure I follow.