XL Ageing Creditor formula help

Question

Hi, I'm in desperate need of some help please as I don't really know much about formulas, so it may be a daft question?

I'm trying to create a formula for an aged creditor report however the ones I've spent hours googling aren't working and it's driving me insane 😞

I want the 'Age' cell to show how many days before its due to be paid but also, once the paid date is entered then cleared the age figure is cleared?

Any help would be much appreciated, thank you!

	Payment Summary		20/12/2021
			Total Paid:	2415
			Aging - Current:	1130
			1 - 30:	1800
			31 - 60:	-1040
			61 - 90:	250
			> 90:	2370
			Total Outstanding:	4510


Payment Due Date	INV AMT Due	Date Paid		Total Paid	Age	Payment Outstanding
01/01/2022	100	05/05/2021	No	100	0	0
13/12/2022	200	12/05/2016	No	200	-358	0
15/12/2015	300		Yes	300	-2197	0
03/10/2021	400		Yes	150	-78	250
25/11/2015	500	15/12/2021	Yes	500	-2217	0
13/11/2015	600		Yes	600	-2229	0
26/12/2015	700		Yes	10	2186	690
30/12/2015	800		Yes	10	-2182	790
14/12/2015	900		Yes	10	42352	890
28/11/2021	100		Yes	10	-22	90
22/02/2021	500		Yes	500	-301	0
20/11/2021	25	21/11/2021	Yes	25	-30	0
22/12/2021	1800		No		2	1800

zed69 · Answer

Martin_Weiss&nbsp;Omg, that's excellent, thank you so much 😄Shouldn't have spent weeks researching but come on here weeks ago.Appreciate it, thanks&nbsp;&nbsp;Peter, I couldn't get yours to work, as it kept coming up with a formula error. I've obviously missed something along the way?I entered the following, but clearly it's incorrect.&nbsp; What have I done wrong please? Thank you=IF(@[O20]&lt;@[L20],@[I20]-N9,"")&nbsp;&nbsp;

martin_weiss · Answer

Hi&nbsp;Zed69&nbsp;&nbsp;this could be one solution:=(A12-TODAY())*ISBLANK(C12)The first part counts the difference between the due date and today.The ISBLANK function checks, if there is something entered in column C.If C12 is blank, it returns true (which equals to 1), so the date difference gets multiplied by 1.If C12 is not blank, it returns false (which equals to 0), so the date difference gets multiplied by 0.&nbsp;

peterbartholomew1 · Answer

Zed69&nbsp;If you convert the data to an Excel Table any formulas will extend to match the data= IF(@[Total Paid] &lt; @[INV AMT Due],
     @[Payment Due Date] - reportDate,
     "")&nbsp;

peterbartholomew1 · Answer

Zed69&nbsp;My guess is that you did not convert the data to a Table (Ctrl/T) or name the cell containing the report date.

Forum Discussion

XL Ageing Creditor formula help

4 Replies

Resources