Aging Inventory

[pkj7461]

Hello,

I hope someone helps me with Aging the inventory based on usage. I have the usage data and am trying to create buckets of 0-30Days, 31-60Days, >60Days based on the date used.
For example, if part#C4871A is used in last 30 days, it should be in 0-30Days. Even if this item is used in last 60 days, the aging should be still set as 0-30Days. Can you please help me with the formula for this type of aging?

Thanks PKJ

 

[Robert Mika]

Coudl you post your data here?


To post your data you can download and install two of the following programs:

HTLMaker

or
Excel Jeanie

or when using Internet Explorer just put a borders around your data in Excel and copy those cells into your post.

 

[pkj7461]

Thanks Robert. Here is my data. The days is calculated as the For this example, the Age should be 0-30Days for first two lines and 31-60days for remaining two lines. Please let me know if you need anything else. Thanks

<TABLE style="WIDTH: 218pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=291><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=17 width=64>Part#</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=79>Usage Date</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=64>Days</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=84>Age</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 height=17>C4871A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>11/2/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>8</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 height=17>C4871A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>5/2/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>192</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=17>C4872A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>9/5/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>66</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=17>C4872A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>4/1/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>223</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68></TD></TR></TBODY></TABLE>

 

[Robert Mika]

Should not the second two lines be set up to >60?
The MIN of C4872A is 66 days which is over 60

Excel Workbook
	A	B	C	D	E
1					10/11/2011
2	C4871A	11/02/2011	8		0-30 Days
3	C4871A	05/02/2011	192		0-30 Days
4	C4872A	09/05/2011	66		>60 Days
5	C4872A	04/01/2011	223		>60 Days
Sheet1

 

[pkj7461]

Thanks Robert. yes you are correct. second two lines be set up >60.

However, when I copy/paste the formula in my excel sheet, it doens't return anything(just #N/A#)....I am doing something wrong. Can you help me with that?

Thank alot,
PKJ.

Should not the second two lines be set up to >60?
The MIN of C4872A is 66 days which is over 60

Excel Workbook
	A	B	C	D	E
1					10/11/2011
2	C4871A	11/02/2011	8		0-30 Days
3	C4871A	05/02/2011	192		0-30 Days
4	C4872A	09/05/2011	66		>60 Days
5	C4872A	04/01/2011	223		>60 Days
Sheet1

 

[Robert Mika]

This is array formula - it should be confirm with Control+Shift +Enter not only Enter.

Hold CTRL+SHIFT keys and press ENTER
And for some reason we lost a part of the tablecolumn H&I)
Please see below:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	*	*	*	*	10/11/2011	*	*	*	*
2	C4871A	11/02/2011	8	*	0-30 Days	*	*	0	0-30 Days
3	C4871A	05/02/2011	192	*	0-30 Days	*	*	30	31-60 Days
4	C4872A	05/09/2011	66	*	>60 Days	*	*	60	>60 Days
5	C4872A	04/01/2011	223	*	>60 Days	*	*	*	*
Sheet1

 

[pkj7461]

Thank you very much!