200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi All
I am trying highlight tyre run out dates by using DOT tyre ages (4 figures) from the wall of the tyres. The first two figures represent the week of manufacture and the second two are the year of manufacture e.g. 1418, 14 (week) 18 (2018). The highlight will then alert me to 3 years after the manufacture date which is when the tyres need replacing.
Many thanks
Bosh
The attached workbook's Sheet1 has a formula in C3 and down which generates week numbers as you find them on your tyres. Enter any valid number in C2 and the rest of the column will fill automatically. This is for testing the formula the steps of development of which are shown in D2:J2.
The formula in D2 was copied from Extended Office. It draws on values in B2 and C2 which are incorrect in my worksheet. Therefore it doesn't work. To make it work, "C2" must be replaced with the formula in E2 and "B2" must be replaced with the formula in F2.
The formula in G2 has these replacements and therefore shows the first day of the week indicated in C2. H2 has a formula which calculates a date 3 years (36 months) later. I2 holds the formula which combined H2 and H2. The date you see there is three years after the first day of the manufacturing week.
In J2 the logic is presented. If the date in I2 is before TODAY() the 3 years have lapsed and the formula returns TRUE. K2 shows the final formula which combines the date calcuated in I2 with the logic in J2. Below is the formula. Please note that I didn't test the formula copied from Extended Office. I merely adapted it.
=(EDATE(MAX(DATE(MOD($C2,100)+2000,1,1),DATE(MOD($C2,100)+2000,1,1)-WEEKDAY(DATE(MOD($C2,100)+2000,1,1),2)+(INT($C2/100)-1)*7+1),36)<TODAY())In the next step I selected C2 and applied a conditional formt to that cell using "a formula to determine which cells to format". I pasted the above formula.
Then, still with C2 selected, I opened the CF Manager and changed the range in the Applies To column from =$C$2 to =$C$2:$C$100. There are other ways of achieving the same result. The point is that it is best not to apply CF piecemeal. Use clear steps to ensure that you have as few rules as possible because they slow down your worksheet.
You will see that the cells in column C which have no week ID in them are all highlighted. The highlight will disappear if a week ID is entered which isn't more than 3 years in the past. I thought this could be useful to show missing IDs. If you prefer to have blank cells not highlighted, either control the Applies To range more exactly or tweak the formula to recognise blank cells.