Stop aging when status changes to Scheduled or Training InProgress from default (WIP)

[shoaibkhan]

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

Assumption:
K2 - DATE ACQUIRED
N2 - AGING (# of days)
O2 - STATUS (with drop down: Training InProgress
Hold
Scheduled
Completed)

Also, to add then the status changes, the aging no should remain as is.

Looking forward for your Help !!

 

[GraH]

In the name manager create "Ageing"

= LAMBDA ( a , b , c , IF ( c = "Completed" , b , TODAY ( ) - a ) )

Book1
	K	L	M	N
2	19/07/2023	10	Completed
3	21/07/2023	10	Completed	Changed the date after setting status "Completed", the calculation remains "10"
4	27/07/2023	2
5	25/07/2023	4	Progress
6	28/07/2023	1
Sheet1

Cell Formulas
Range	Formula
L2:L6	L2	=Ageing(K2,L2,M2)

 

[shoaibkhan]

Hello ,

its not working out for me. Kindly let me know if i am missing anything

In the name manager create "Ageing"

= LAMBDA ( a , b , c , IF ( c = "Completed" , b , TODAY ( ) - a ) )

Book1
	K	L	M	N
2	19/07/2023	10	Completed
3	21/07/2023	10	Completed	Changed the date after setting status "Completed", the calculation remains "10"
4	27/07/2023	2
5	25/07/2023	4	Progress
6	28/07/2023	1
Sheet1

Cell Formulas
Range	Formula
L2:L6	L2	=Ageing(K2,L2,M2)

 

[GraH]

You create the formula in the name manager and name it ageing. In the cell you use the function ageing.