Aging formula with variables/conditions

[dmxcasper2]

Link to the file: (Please see tab called "Master")
https://drive.google.com/open?id=186WZJRjc4q7kt4NyivnEsmzQOZ9atg9q


Problem statement:

(Option A - Preferred)
Step 1.) If a date exists in the following columns (O, R, U, X, AA), display the latest date in column AJ.
Step 2.) In column AS display numerical aging from the following logic statement (date in column AJ minus date in column AK, if AK is blank then minus it from ()today formula).


(Option B)
In column AS, display sum of AJ minus AK, however if column AK is blank, then display sum of AJ minus today's date.


I've been at this for hours trying out various combinations of -sumif formulas, but I can't seem to apply the above logic to a concise statement.

 

[RasGhul]

Hi Casper,

Can you use this?

Book1
	O	R	U	X	AA	AJ	AK	AS
1	no History	no History	06-11-18	24-11-18		06-11-18	07-11-18	-1
2	no History	no History	01-11-18	17-11-18		01-11-18	07-11-18	-6
3	no History	no History	29-09-18	26-11-18		29-09-18	14-11-18	-46
4	no History	no History	01-11-18	24-11-18		01-11-18	07-11-18	-6
5	no History	no History	01-11-18	24-11-18		01-11-18		-15
Sheet1

Cell Formulas
Range	Formula
AS1	=IF(AK1="",AJ1-TODAY(),AJ1-AK1)
AJ1	{=IF(O1:X1>1,SMALL(O1:X1,1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[dmxcasper2]

Thank you RasGhul.

Also, for invalid entries that cannot be calculated because there are no dates those columns, how could substitute invalid "-43420" with a blank data in the aging cell?

So if aging cannot be calculated because there are no dates in column AJ and AK, then the result should be "blank" in the aging cell (AS).

 

[RasGhul]

You could use,

=IF(AND(AJ1="",AK1=""),"",IF(AK1="",AJ1-TODAY(),AJ1-AK1))

The original AJ formula should include the AA column;

=IF(O1:AA1>1,SMALL(O1:AA1,1),"")

(Remember to use CTRL+Shift+Enter for array)

**Note that the AJ formula may be prone to errors if numeric values are entered into the hidden cells between O1-AA1.