Help needed for 52 Week cycle & Conditional Formatting

Hi all,

Hopefully someone can help me. I'm working on a spreadsheet which shows a list of vehicles and when their services are due by week number with a color coded system controlled by conditional formatting. For example, If a vehicle is due service on week 12, on week 10 the cell will change to 'Yellow' to book the vehicle in for service, Week 11 will change to 'amber' as a pre warning then on Week 12 the cell will change to 'Red' which will be the week it goes in for service. Please check 'Example 1' for a visual representation of this

I've currently set it all up and all works bar any services that are due on Week 1. As you can see in 'Example 2' if a service is due on Week 1 and the current week is 52 then the color system does not kick in as the conditional formatting does not understand that their is a 52 week cycle. So if the week is 52 and a service is due on weeks 1 & 2 then the amber and yellow status does not kick in.

If anyone can shed any light on this or point me in the right direction it would be greatly appreciated. If you need further information please feel free to ask

Cheers,

Josh

I don't have time to dig into this now, but I am thinking you modify how you do it just a little. Possibly having a cell that calculates what number to use to make the service due yellow, red, etc.

For example, an if statement .....=if(current week <50, current week +2, if(current week = 51, 1, 2)) (change current week to the cell calculating your current week. Then point your conditional formatting to that cell so when the week service is due equals the new cell it turns yellow. You would have to do the same for the other colors.

Let me know if you want me to look at it over the weekend for you.

Marty

Thanks for all your replies

Been trying to understand this but can't get my head around it. Any chance you could provide an example I could play around with?

Many thanks,

Josh