How do I tell Excel to calculate an expiry function from a 4 digit Week/Year number?

[UnderTheArmour]

Hi all ? I'm trying to get Excel to calculate an expiry from a 4 digit WeekMnth number...

I want it to read the number as WeekYear, and calculate 9 years and 9 months from it to either create a warning, or change the colour of a number of cells or something...

It's for a sheet I use all the time, so if it just changes the colour of a line of key cells in a given row that would be enough... It's to calculate the 10 year expiry dates for tires that I have on multiple vehicles...

So for example when it hits 9 years, 6 months past the start date it could go yellow, orange at 9 years 9 months, and red when at the 9 years, 51 weeks point or something...

Sorry to all that I can't use the awesome mini-sheet feature as I'm doing all of this through handheld Android devices...

Thanks in advance, Mick ??

 

[Toadstool]

Hi UnderTheArmour,

I've converted to calendar dates and highlighted date within 180, 90 and 7 days.

UnderTheArmour.xlsx
	A	B	C	D	E	F
5	Tyre Size	Brand	Type	Qty	DOT	EXP
6	333/22R	Noskid	Winter	4	2217	22-May-27
7	333/22R	Noskid	Summer	3	3913	23-Sep-23
8	333/22R	Noskid	All Season	5	1918	07-May-28
9	333/22R	Nopop	Winter	4	1814	28-Apr-24
10	333/22R	Nopop	Summer	4	5321	27-Dec-31
11	333/22R	Noleak	All Season	3	2612	18-Jun-22
12	333/22R	Noleak	Summer	4	101	01-Jan-11
13	333/22R	Noleak	Summer	4	312	09-Jan-22
14	333/22R	Noleak	Summer	4	2211	23-May-21
15	333/22R	Noleak	Summer	4	2212	21-May-22
16	333/22R	Noleak	Summer	4	112	01-Jan-22
17	333/22R	Noleak	Summer	4	2612	18-Jun-22
18	333/22R	Noleak	Summer	4	312	09-Jan-22
19
Sheet1

Cell Formulas
Range	Formula
F6:F18	F6	=IF(E6="","",EDATE(MAX(DATE(RIGHT(E6,2)+2000,1,1),DATE(RIGHT(E6,2)+2000,1,1)-WEEKDAY(DATE(RIGHT(E6,2)+2000,1,1),2)+(LEFT(TEXT(E6,"0000"),2)-1)*7+1),120))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F6:F999	Expression	=AND(F6<>"",F6-TODAY()>90,F6-TODAY()<=180)	text	NO
F6:F999	Expression	=AND(F6<>"",F6-TODAY()>7,F6-TODAY()<=90)	text	NO
F6:F999	Expression	=AND(F6<>"",F6-TODAY()<=7)	text	NO

 

[UnderTheArmour]

Wow Toadstool, that's some fine, slick Excelling!! EXCEL-lent! ??️

I'm still having trouble with the conditional formatting as this is my option...

 

[Toadstool]

As long as your first screen image was accurate then it looks like you want to highlight columns A to F based on the EXP date in column F.

If that's the case then your "Applies to" range should be column A and the first row of data through column F and the last possible row of data, e.g. $A$6:$F$999

Then you will need three Custom Formula rules, one for each colour, which are shown below under "Cells with Conditional Formatting".

UnderTheArmour.xlsx
	A	B	C	D	E	F
5	Tyre Size	Brand	Type	Qty	DOT	EXP
6	333/22R	Noskid	Winter	4	2217	22-May-27
7	333/22R	Noskid	Summer	3	3913	23-Sep-23
8	333/22R	Noskid	All Season	5	1918	07-May-28
9	333/22R	Nopop	Winter	4	1814	28-Apr-24
10	333/22R	Nopop	Summer	4	5321	27-Dec-31
11	333/22R	Noleak	All Season	3	2612	18-Jun-22
12	333/22R	Noleak	Summer	4	101	01-Jan-11
13	333/22R	Noleak	Summer	4	312	09-Jan-22
14	333/22R	Noleak	Summer	4	2211	23-May-21
15	333/22R	Noleak	Summer	4	2212	21-May-22
16	333/22R	Noleak	Summer	4	112	01-Jan-22
17	333/22R	Noleak	Summer	4	2612	18-Jun-22
18	333/22R	Noleak	Summer	4	312	09-Jan-22
19
20
Sheet1

Cell Formulas
Range	Formula
F6:F18	F6	=IF(E6="","",EDATE(MAX(DATE(RIGHT(E6,2)+2000,1,1),DATE(RIGHT(E6,2)+2000,1,1)-WEEKDAY(DATE(RIGHT(E6,2)+2000,1,1),2)+(LEFT(TEXT(E6,"0000"),2)-1)*7+1),120))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6:F999	Expression	=AND($F6<>"",$F6-TODAY()>90,$F6-TODAY()<=180)	text	NO
A6:F999	Expression	=AND($F6<>"",$F6-TODAY()>7,$F6-TODAY()<=90)	text	NO
A6:F999	Expression	=AND($F6<>"",$F6-TODAY()<=7)	text	NO