# Formula/Conditional formatting to highlight upcoming oil change week

1. ## Formula/Conditional formatting to highlight upcoming oil change week

I have an excel file which shows readings of oil change.
I want to highlight the week numbers when Oil date is due as per total running hours (cell G2).
The formula or conditional formatting should highlight week in which oil is replaced and next change is due. For example: if oil is changes in june last week, it should highlight june 4th week and it should calculate next change date and highlight respective week.

Attached is sample sheet

2. ## Re: Formula/Conditional formatting to highlight upcoming oil change week

How do you calculate when the next oil changes are due?

Pete

3. ## Re: Formula/Conditional formatting to highlight upcoming oil change week

Hi Pete,

Next Oil changes will be due on Reading in cell G2, Moreover, i have added some formulas to column K,L,M for better understanding and tracking. Now we have per day run in column M through which we can predict when will it reach to reading in Cell G2.
Hope you will understand it now. Thank you

4. ## Re: Formula/Conditional formatting to highlight upcoming oil change week

Please manually add the results you expect for at least the first row of data (row 5) so that we can see what you are aiming to achieve.

5. ## Re: Formula/Conditional formatting to highlight upcoming oil change week

As per AliGW request, i have inserted comments for achieving required results. Attached is snapshot and sample file.
Green highlighted cells shows oil change date(current) and red highlighted shows upcoming oil change week
Annotation 2022-04-30 151843.png

6. ## Re: Formula/Conditional formatting to highlight upcoming oil change week

This proposal changes the setup, so if that isn't negotiable then please feel free to stop reading at this point.
1. The dates in row 2 are unmerged and display the first Sunday of each week using: =EOMONTH(C3,-1)+1-WEEKDAY(EOMONTH(C3,-1)+1)+1 in cell N2 and =SUM(N2,7) in cells O2:BM2
2. The week numbers in row 3 are populated using: =WEEKNUM(N2)
3. The conditional formatting rule for green is: =N\$3=\$C\$2
4. Cells N5:BM7 are populated using: =IF(N\$2<\$C\$3,0,SUM(M5,1)) and formatted ;;;
5. The conditional formatting rule for red is: =M5=ROUND(\$L5/7,0)
Let us know if you have any questions.

7. ## Re: Formula/Conditional formatting to highlight upcoming oil change week

Dear JeteMC, Thank you for your response, Indeed it is working but when i extended the formula to more cells, it again shows GREEN on 26 june 22, it should not reshow already oil changed date but rather it has to show next oil change(which will be shown as red) and so on. CAn you please look into it. Attached is file and screenshot
Attachment 779279

8. ## Re: Formula/Conditional formatting to highlight upcoming oil change week

1. The formula in rows 5:7 is modified to read: =IF(N\$2<\$C\$3,0,MOD(SUM(M5,1)-1,ROUND(\$L5/7,0))+1)
2. The rule for red is changed to read: =AND(N\$2>\$C\$3,M5=ROUND(\$L5/7,0))
3. The rule for green is changed to read: =AND(N\$3=\$C\$2,YEAR(N\$2)=YEAR(\$C\$3))
Let us know if you have any questions.

9. ## Re: Formula/Conditional formatting to highlight upcoming oil change week

Thank you JeteMc, Its now working fine. Really appreciate your help. I will close the thread tomorrow after applying these formulas on actual file and update here as well. I am adding ++ to you right now for your kind help and time.
Regards
Zahid

10. ## Re: Formula/Conditional formatting to highlight upcoming oil change week

Hi JeteMc,
Sorry i got a little late. One thing if it can be done/added please. Is it possible, to calculate next change date when remaining hours( column J) reaches 145. I know its getting a little demanding but my boss told me to do it, please if its possible, i shall be thankful. if possible or not, i will close this thread by tomorrow. thank you once again
Capture.PNG

11. ## Re: Formula/Conditional formatting to highlight upcoming oil change week

Not sure that I completely understand, but it sounds as if your boss wants the next oil change to be displayed when the value in column J reaches -145 and remain displayed until the date in cell C3 is changed.
If that is the case then the rule for red could be changed to read: =AND(\$J5>=-145,N\$2>\$C\$3,M5=ROUND(\$L5/7,0))
Let us know if you have any questions.

12. ## Re: Formula/Conditional formatting to highlight upcoming oil change week

Great Thank you JeteMC for your response and time. It helped a lot

13. ## Re: Formula/Conditional formatting to highlight upcoming oil change week

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

There are currently 1 users browsing this thread. (0 members and 1 guests)