+ Reply to Thread
Results 1 to 13 of 13

Formula/Conditional formatting to highlight upcoming oil change week

  1. #1
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    216

    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.

    NEED HELP PLEASE

    Attached is sample sheet
    Attached Files Attached Files
    Last edited by Zahid0111; 04-29-2022 at 11:57 AM.

  2. #2
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Botswana
    MS-Off Ver
    Professional Plus 2019
    Posts
    938

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

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

    Pete

  3. #3
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    216

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

    Hi Pete,
    Thanks for your reply.

    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
    Attached Files Attached Files
    Last edited by Zahid0111; 04-30-2022 at 02:19 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,497

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    216

    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
    Attached Files Attached Files
    Last edited by Zahid0111; 04-30-2022 at 06:39 AM.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    216

    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
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    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.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    216

    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. #10
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    216

    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. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    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. #12
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Pakistan
    MS-Off Ver
    365
    Posts
    216

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

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

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Highlight Upcoming Birthdays with Conditional Formatting
    By Nebulosity in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-20-2018, 08:37 AM
  2. Replies: 7
    Last Post: 02-24-2016, 11:34 AM
  3. [SOLVED] Show upcoming date - Excel formula
    By Sinep D in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-29-2015, 06:08 PM
  4. [SOLVED] Conditional Formatting to show week over week improvement/decline
    By erikw48 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-15-2015, 10:22 AM
  5. Conditional Formatting - Upcoming Dates and Blanks
    By susstu in forum Excel General
    Replies: 6
    Last Post: 10-29-2014, 04:45 PM
  6. Replies: 6
    Last Post: 06-06-2014, 07:40 AM
  7. Replies: 11
    Last Post: 05-04-2014, 08:28 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1