Stop a countdown of days based on a column

Hello,

I am trying to calculate the ageing of some services which are identified by ID. To calculate the ageing of each service I did the formula: DateTimeDiff([Initial_Date],Datetimetoday(),"days"). However, once the column "Status" is Complete, the ageing calculation needs to stops and I have to keep the count days between the Initial Date (which is fixed) until the last day which was pending the service.

Does anyone knows how can achieve this result?

Thanks

example.xlsx

Accepted answers

TomWelgemoed

Hi @dcsrobayo (Diana),

Thanks for the example.

OK, then the issue is that you're effectively "losing" data in the latest file, unless you are still able to retrieve the previous day's file?

I'm going to assume that you can still access the previous day's file - otherwise you would have to look to store copies yourself to maintain history. Which is important to be able to tell the latest date of any record.

I've made the example a little more complex so that we can simulate some scenarios where records are changing from 1 day to the next. Please see the attached workflow - there are 2 containers:

1. What you'd need to do on the first day to create the baseline

2. What you would do the following day to use that baseline

Let me know if you have questions - I didn't add duplicate records of the same ID, but this should work for that too, barring some potential duplication which you'd need to manage.

Date diff.png

Date Diff.yxzp

All comments

TomWelgemoed

Hi @dcsrobayo ,

I think you're halfway there.

The only bit you need is to know what the latest Pending date was and then to put it into a conditional statement. Please see the attached example and the image below.

Regards,

Tom

Date diff.png

Date Diff.yxzp

dcsrobayo

Hi @TomWelgemoed

Thanks for your response. I applied into my model but when all the status rows are under "Complete" the join does not generate any result because there is not pending status. In addition, the column Latest_Pending_Date column disappears. Do you know there is another way to do it?

Many thanks,

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703