Excel - unusual date calculation

[yoyo54321]

Hi

All help would be most appreciated.

I'm working with a product that comes with a manufacturing date code of a 2 digit year code and 2 digit week number example 2143 and i need add 52 weeks so it returns the same format 2243 but takes into account some years have a different amount of weeks.

So i need to enter the 4 digit manufacturing date and be able to enter that amount of weeks they expire because some have 52 weeks but others have 78 weeks

Column A Column B Column C
2143 x 52 = 2243
2151 x 78 =

Thanks in advance

Kev

 

[DRSteele]

Welcome to the Forum.

Please provide more examples for us. I can't understand what you mean by "78 weeks."

 

[yoyo54321]

Welcome to the Forum.

Please provide more examples for us. I can't understand what you mean by "78 weeks."

Thanks for taking a look. We have 2 products 1 expires after 52 weeks and another after 78 weeks from the date the manufacturing. I'm struggling because they use the manufacturing date of 2 a digit year code and week number.

 

[DRSteele]

Try this. Without more examples to review, I can only guess.

MrExcel posts19.xlsx
	C	D	E	F	G	H	I	J	K
5	old code	left	right	add	years	year	week	new code	new code (single cell)
6	2143	21	43	52	1	22	43	2243	2243
7	2151	21	51	78	2	23	25	2325	2325
Sheet13

Cell Formulas
Range	Formula
D6:D7	D6	=LEFT(C6,2)
E6:E7	E6	=RIGHT(C6,2)
G6:G7	G6	=INT((E6+F6)/52)
H6:H7	H6	=D6+G6
I6:I7	I6	=MOD(E6+F6,52)
J6:J7	J6	=NUMBERVALUE(H6&I6)
K6:K7	K6	=NUMBERVALUE(LEFT(C6,2)+INT((RIGHT(C6,2)+F6)/52)&MOD(RIGHT(C6,2)+F6,52))

 

[jtakw]

Hi,

This is a bit more complicated than I anticipated:

Book3.xlsx
	A	B	C
1	Start Date Code	Add Weeks	New Date Code
2	2143	52	2243
3	2151	78	2324
Sheet987

Cell Formulas
Range	Formula
C2:C3	C2	=TEXT(DATE(2000+LEFT(A2,2),1,-2)-WEEKDAY(DATE(2000+LEFT(A2,2),1,3))+(RIGHT(A2,2)-1)*7+B2*7,"yy")&WEEKNUM(DATE(2000+LEFT(A2,2),1,-2)-WEEKDAY(DATE(2000+LEFT(A2,2),1,3))+(RIGHT(A2,2)-1)*7+B2*7)

 

[yoyo54321]

Hi. That looks like it works but i need to confirm on Monday

Thanks very much for your help

 

[Tetra201]

@yoyo54321: What is the correct manufacturing code for 1-Jan-2021: 2101 or 2053?

 

[yoyo54321]

@yoyo54321: What is the correct manufacturing code for 1-Jan-2021: 2101 or 2053?

Hi it would be 2053 based on the calender using isoweeknum

 

[Tetra201]

Thank you for the clarification. See if the following formula works for you:

=TEXT(DATE(20&LEFT(A2,2),1,-2)-WEEKDAY(DATE(20&LEFT(A2,2),1,4),2)+RIGHT(A2,2)*7+B2*7,"yy")&WEEKNUM(DATE(20&LEFT(A2,2),1,-2)-WEEKDAY(DATE(20&LEFT(A2,2),1,4),2)+RIGHT(A2,2)*7+B2*7,21)