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

[TABLE="width: 731"]
<colgroup><col><col span="3"><col span="4"><col><col span="2"></colgroup><tbody>[TR]
[TD]Date [/TD]
[TD] Balance[/TD]
[TD] Current[/TD]
[TD] P1[/TD]
[TD] P2[/TD]
[TD] P3[/TD]
[TD] P4[/TD]
[TD] Older[/TD]
[TD]Due Date[/TD]
[TD]Payment Terms[/TD]
[TD]Days Overdue[/TD]
[/TR]
[TR]
[TD]19/09/2017[/TD]
[TD] 2,160.00[/TD]
[TD] 2,160.00[/TD]
[TD] 2,160.00[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD][/TD]
[TD="align: right"]19-10-2017[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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

Date

Balance

Current

P1

P2

P3

P4

Older

Due Date

Payment Terms

Days Overdue

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]19.09.2017[/TD]
[TD="align: right"]2160[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=e2efda]#e2efda[/URL] , align: right"]2160[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]19.10.2017[/TD]
[TD="align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=e2efda]#e2efda[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=e2efda]#e2efda[/URL] , align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]20.10.2017[/TD]
[TD="align: right"]2160[/TD]
[TD="align: right"]2160[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]19.10.2017[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]19.11.2017[/TD]
[TD="align: right"]2160[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2160[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]19.10.2017[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]19.12.2017[/TD]
[TD="align: right"]2160[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2160[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]19.10.2017[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]19.01.2018[/TD]
[TD="align: right"]2160[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2160[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]19.10.2017[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]19.02.2018[/TD]
[TD="align: right"]2160[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2160[/TD]

[TD="align: right"]19.10.2017[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]4[/TD]

</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