Calculate/display age of stock

[User100]

Hi all!

Hope somebody can help me with this, see attached snap. I want to calculate how old the daily stock is. What I want in the line 'age of stock' is the total kilograms which are obsolete. In this example the product expires in 3 days, so consider what will be older than 3 days have to be thrown away and afterwards will be booked as manual correction if there is obsolete stock. From the stock in this example of Tuesday 25.01, after 3 days, so on Saturday 29.01 we have to throw away 2300 kg, this is filled in now manually. Is there an formula which can calculate this automatically and show me the obsolete stock after the expiration day?

I also want to see not only the obsolete stock after the expiration day, but maybe it is useful to see each day in how many days the stock will be produced, maybe this is easier than above solution, I am just try to figure out what the easiest way is.

If someone has a better idea to solve this in Excel please let me know, I am not very good in excel, still learning, so please explain and try explain me as beginner, many thanks and well appreciated!

 

[BSALV]

can you give an example with the XL2BB-tool, because i have no idea how you calculate for example the stock.
I fear to need a VBA-solution, an UDF, is that a problem ?

 

[etaf]

i have not worked out why its 2300 and NOT 2800
you could use a sumproduct to add up based on the header and the days

=SUMPRODUCT(($E$4:G14)*($E$1:G1=H1-$B$2))
if only want the sum of the header 3 days ago
OR
=SUMPRODUCT(($E$4:G14)*($E$1:G1<=H1-$B$2))
for anything older than 3 days

although i suspect this is a more complicated as you have 2300
so along with posting using XL2BB or on a share like dropbox/onedrive
with an explanation of how you get to the result with a few more examples

you may need as mentioned VBA _ not my area

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1					1/24/22	1/25/22	1/26/22	1/27/22	1/28/22	1/29/22	1/30/22	1/31/22
2	Ep Days	3						2800	2813	2813	2813	2813
3		grow1
4		grow2			600
5		grow3				1
6		grow4
7		grow5			2000	5
8		grow6
9		grow7
10		grow8				7
11		grow9			200
12		grow10
13		grow11
14		grow12
Sheet1

Cell Formulas
Range	Formula
H2	H2	=SUMPRODUCT(($E$4:G14)*($E$1:G1=H1-$B$2))
I2:L2	I2	=SUMPRODUCT(($E$4:H14)*($E$1:H1<=I1-$B$2))

 

[User100]

Hi, Thanks for the feedback! I have problems to share the file, is there a website which I can share a link with, WeTransfer and Dropbox are not for free anymore it seems..

 

[etaf]

dropbox basic is free for 2gb
onedrive free for 5gb
google drive
google docs

 

[BSALV]

i did something similar here, also obsolete stock, but had no reaction afterwards, can happen ...
Is a VBA-solution allowed ?

FIFO based on date only...

Good morning everyone, I've spent nearly a whole day on this but can't seem to get it right... I'm trying to make a running spreadsheet that works on the FIFO principles, but with the same unit costs. The issue is that the status of the goods changes from "Certified" no "non-certified" after...

www.mrexcel.com

 

[User100]

HI all, Can you check attached link? See other calculations of stock attached. Would be great if someone can help with this?

Example age of product.xlsx

docs.google.com

Kind regards,
Ties

 

[User100]

A VBA-solution seems not to work properly if we share the file via sharepoint, can this be calculated without a VBA-solution?

 

[BSALV]

Example

Example age of product.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
5					23/jan	24/jan	25/jan	26/jan	27/jan	28/jan	29/jan	30/jan	31/jan	1/feb	2/feb	3/feb	4/feb	5/feb	6/feb
6					Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
7	Product 1		Total Production			1.000	1.000	1.000	2.000	3.000	400	0	1.200	14.000	1.500	1.600	1.800	200	0
8	Expiration in days	3	Stock		600	2.400	8.300	12.300	11.300	6.500	1.600	600	1.700	0	0	3.000	1.200	1.200	200
9		0	stock made day0		600	2.400	6.900	5.000	1.000	500	100	0	1.700	0	0	3.000	0	200	0
10		1	stock made day-1		0	0	1.400	6.900	5.000	1.000	500	100	0	0	0	0	1.200	0	200
11		2	stock made day-2		0	0	0	400	5.300	5.000	1.000	500	0	0	0	0	0	1.000	0
12			expirated		0	0	0	0	0	2.300	4.600	1.000	0	0	0	0	0	0	1.000
13			Required		0	0	0	0	0	0	0	0	0	10.300	900	0	0	0	0
14			Age of stock		0,00	0,00	0,17	0,63	1,38	1,69	1,56	1,83	0,00	-	-	0,00	1,00	1,67	1,00
15		Manual correction								-2300
16		Grower 1	Product specification												600
17		Grower 2	Product specification			600	900
18		Grower 3	Product specification					3000	400	200			200
19		Grower 4	Product specification				2000						500			600
20		Grower 5	Product specification			2000		2000			100
21		Grower 6	Product specification						600				600			4000		200
22		Grower 7	Product specification										1000	1000
23		Grower 8	Product specification				4000			300
24		Grower 9	Product specification			200								1000
25		Grower 10	Product specification
26
27						2800	6900	5000	1000	500	100	0	2300	2000	600	4600	0	200	0
Sheet1

Cell Formulas
Range	Formula
F5:S5	F5	=E5+1
F8:S8	F8	=SUM(F9:F11)
F9:S9	F9	=MAX(0,MIN(0,SUM(E9:E$11)-F$7)+F27)
F10:S10	F10	=MAX(0,SUM(E9:E$11)-F$7-F11-F12)
F11:S11	F11	=MAX(0,SUM(E10:E$11)-F$7-F12)
F12:S12	F12	=MAX(0,E11-F7)
F13:S13	F13	=MAX(0,F7-SUM(E9:E11)-F27)
E14:S14	E14	=IFERROR(SUMPRODUCT($B$9:$B$11,E9:E11)/SUM(E9:E11),"-")
F27:S27	F27	=SUM(F16:F25)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E13:L13	Cell Value	>0	text	NO
F14	Cell Value	>$D$7	text	NO
F14	Cell Value	>$D$7	text	NO
M14	Cell Value	>$D$7	text	NO
M14	Cell Value	>$D$7	text	NO
M13:S13	Cell Value	>0	text	NO

 

[User100]

Thanks, for helping, stock of each days should be older if you look at the production per day and stock level per day. Is the age of stock displayed in days?

The Expirated formula looks good, is it possible to make 1 formula for this without adding extra rows?