Ageing Formula

[vasudev_kamat]

Hi Friends,

I need support on excel formula. I am using ageing calculation formula based on customer type. If it is Third party customer I need age buckets like >15 days, Not Due, Less than 15 days. and if it is Inter Company customer again >30 days, Not Due, less than 30 days.Row B has value for type of customer. Overall due date in Row D. and ageing formula required in cell E.
Attached screenshot. Please support with formula.

 

[Yongle]

oops

 

[Yongle]

Book1
	A	B	C	D	E
1			Month End	31/07/2020
2	Customer	Type	Due date	Ageing	Bucket
3	abc	Third Party	01/07/2020	30	>15 days
4	def	Inter Company	15/08/2020	-15	Not Due
5	ghi	Third Party	01/01/2020	212	>15 days
6	jkl	Inter Company	15/05/2020	77	>30 days
7	mno	Inter Company	30/06/2020	31	>30 days
8	vol	Inter Company	01/07/2020	30	Not Due
9	toc	Third Party	15/07/2020	16	>15 days
10	hju	Third Party	16/07/2020	15	Not Due
Sheet1

Cell Formulas
Range	Formula
D3:D10	D3	=$D$1-C3
E3:E10	E3	=IF(B3="Third Party",IF(D3<16,"Not Due",">15 days"),IF(D3<31,"Not Due",">30 days"))

 

[vasudev_kamat]

		Month End Date	31-Jul-20​	Formula Required
Customer Name	Customer Type	Payment Due date	Ageing	Ageing Bucket
abc	Third Party	01-Jul-20​	30​	>15 days
xyz	Inter Company	15-Aug-20​	-15​	Not Due
dkz	Third Party	01-Jan-20​	212​	>15 days
prz	Inter Company	15-May-20​	77​	>30 days
mrq	Third Party	10-Oct-20​	-71​	Not Due