Moving Dates in quarters

[kevzmm]

hi,

Just need a bit of help with some data for over due invoices.

for example the data will look like

Date	Balance	Current	P1	P2	P3	P4	Older	Due Date	Payment Terms	Days Overdue
19/09/2017	2,160.00	2,160.00	2,160.00	-	-	-		19-10-2017	30	15

<colgroup><col><col span="3"><col span="4"><col><col span="2"></colgroup><tbody>
</tbody>

and what i need to be able to do is where the over due date is move it across according to how many days it is over due.

for example anything below 30 in current. Above 30 P1 but not greater than 59. and so on.

Thanks,

Kevin

 

[AlanY]

for example anything below 30 in current. Above 30 P1 but not greater than 59. and so on.

need a bit more explanations regarding these.

are the P2, P3 etc come into play?

 

[kevzmm]

need a bit more explanations regarding these.

are the P2, P3 etc come into play?

Hi Apologies

What i am looking for is formulae that can help me put these numbers into the correct category.

this is the scenarios-

1. if the invoice is over due by 30 days or less it goes into current.
2. if the invoice is over 31 but below 59 days into p1
3. if the invoice is over 60 days but less that 89 days p2
4. if the invoice is over 90days but less than 119 p3
5 and then anything above 120


thanks,

 

[AlanY]

ok, may be this

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Balance	Current	P1	P2	P3	P4	Older	Due Date	Payment Terms	Days Overdue
2	19/09/2017	2,160.00		2,160.00					19/10/2017	30	35
Sheet1

Cell Formulas
Range	Formula
C2	=IF($K$2<=30,$B$2,"")
D2	=IF(AND($K$2>30,$K$2<=60),$B$2,"")
E2	=IF(AND($K$2>60,$K$2<=90),$B$2,"")
F2	=IF(AND($K$2>90,$K$2<=120),$B$2,"")
G2	=IF($K$2>120,$B$2,"")

 

[kevzmm]

thank you so much exactly what i needed

 

[AlanY]

you're welcome

 

[shift-del]

Kevzmm,

a more flexible solution.
Tabelle1 (2)

	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Balance	Current	P1	P2	P3	P4	Older	Due Date	Payment Terms	Days Overdue
2	19.09.2017	2160	2160	0	0	0	0		19.10.2017	30	0	0
3	20.10.2017	2160	2160	0	0	0	0		19.10.2017	30	1	0
4	19.11.2017	2160	0	2160	0	0	0		19.10.2017	30	31	1
5	19.12.2017	2160	0	0	2160	0	0		19.10.2017	30	61	2
6	19.01.2018	2160	0	0	0	2160	0		19.10.2017	30	92	3
7	19.02.2018	2160	0	0	0	0	2160		19.10.2017	30	123	4

<tbody>
</tbody>

Formeln der Tabelle

Zelle Formel C2 =(COLUMN()-3=$L2)*$B2 K2 =MAX(0,A2-I2) L2 =QUOTIENT(K2,J2) <tbody> </tbody>

<tbody>
</tbody>

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8