Excel Formula for Date Ageing with given condition

[ryanjuzz]

Hi Guys,

Need help on condition formula for Aging of date with conditions, example below.

If Date below with Status = Open, Hold or In Progress populate on Ageing field the following (if 1 to 14 days = <14, if more 14 days but less than 30 days = <30, if more than 30days but less than 45 days = <45 and more than 45 days = >45)

Followed by a formula that will input the ageing in days if STATUS again is Status = Open, Hold or In Progress

BUT if Status is Closed populate on Ageing field FALSE and Days Outstanding is 0

NEED FORMULA for Both AGEING and DAYS OUTSTANDING

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Date[/TD]
[TD]Status[/TD]
[TD]Issue[/TD]
[TD]Ageing[/TD]
[TD]Days Outstanding[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jan 8, 2018[/TD]
[TD]Open[/TD]
[TD]OS problem[/TD]
[TD]<30[/TD]
[TD="align: center"]28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Dec 7, 2018[/TD]
[TD]In Progress[/TD]
[TD]Virus issue[/TD]
[TD]>45[/TD]
[TD="align: center"]59[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Oct 6, 2017[/TD]
[TD]Hold[/TD]
[TD]Budget porblem[/TD]
[TD]>45[/TD]
[TD="align: center"]123[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Feb 3, 2018[/TD]
[TD]Closed[/TD]
[TD]CPU replacement[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance to the BIG help...

Ry

 

[snjpverma]

What will be the output if it is 45 days?

 

[snjpverma]

how do you calculate the days outstanding?