Stop aging when status changes

[kvillanueva21]

Hi! I am seeking for a help on how to stop aging when status changes

Assumption:
A32 - DATE ACQUIRED
G32 - AGING (# of days)
I32 - STATUS (with drop down: Successfully Closed
On Hold
Inactive
Closed by Client
Ongoing

Looking forward to your help!

 

[Yongle]

Try something like this in cell G32 (ensure cell number format is GENERAL)

=IF(OR(I32="Successfully Closed",I32="Closed by Client"),"CLOSED",TODAY()-A32)

 

[kvillanueva21]

Hi Yongle, thanks for the help, It worked! However, I also want to know what would be the formula if I want to stop the aging some time in the past.
E.g. A32 is 02/29/2017 and I want to stop the aging by the date of 04/16/2018

Here's my objectives for my excel file:
1. I want to use the same formula for the all the status then once the status changes to "Successfully Closed and Closed by Client", the aging will stop but if the status is at ongoing, the aging continues
2. E.g version of the template that I am creating:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date Acquired[/TD]
[TD]Age[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]02/29/2017[/TD]
[TD][/TD]
[TD]Closed by Client[/TD]
[/TR]
[TR]
[TD]02/25/2018[/TD]
[TD]89[/TD]
[TD]Ongoing[/TD]
[/TR]
</tbody>[/TABLE]

Please take note that I don't strictly put a tab for the Date Closed.

Let me know if you understand what I want to achieve for my excel file.

Looking forward to your help again.
Thanks!

 

[Yongle]

I also want to know what would be the formula if I want to stop the aging some time in the past.
E.g. A32 is 02/29/2017 and I want to stop the aging by the date of 04/16/2018

We need to find a method to tell Excel when to stop aging
How did you arrive at this date? (04/16/2018)
- is it based on another date within the database (eg 10 days after x) or is it the time that the status changed?
- if it's the former then a formula can be used
- if it's the latter then a timestamp needs to be entered somewhere when the status changes

Which one is it?

 

[kvillanueva21]

The 04/16/2018 is the date when the status changed.

Is there any way that I can send you an excel file for you see the template that I am doing.

 

[Yongle]

Use another cell in the same row to capture the "closed" date
I used J32
J32 remains empty until status changes to closed
manually enter the date in J32 when status changes to closed

and G32 formula
=IF(OR(I32="Successfully Closed",I32="Closed by Client"),J32-A32,TODAY()-A32)

 

[kvillanueva21]

It's not working. Can I manually input the date into the formula?

 

[Yongle]

The formula does work
- you have made a mistake

The formula I gave you before was this - you got it working
=IF(OR(I32="Successfully Closed",I32="Closed by Client"),"CLOSED",TODAY()-A32)

The new formula is very similar
=IF(OR(I32="Successfully Closed",I32="Closed by Client"),J32-A32,TODAY()-A32)

The value in J32 must be input as a date (not text) and it will work for you

 

[kvillanueva21]

I am actually using the company's template so I cannot change anything. I cannot add another cell "J32".

This is my major challenge because I cannot input the "Date closed".

 

[Yongle]

You could use this

=IF(OR(I32="Successfully Closed",I32="Closed by Client"),DATEVALUE("15 June 2018")-A32,TODAY()-A32)

and manually amend the date inside " " each time