I am working with Inspections Dates and I have a range of dates to work within to have these inspections completed. I am trying to format the cells that contain the due dates to where they will change color when I am in the due date range window to execute these inspections. See the attached excel sample file.
Hopefully I have given enough information to where someone can help me with the conditional formatting.
I appreciate any help.
Regards,
Ray
Last edited by rhoover7420; 03-17-2019 at 10:55 AM. Reason: Added sample file
You'll need three conditional formatting rules. 1 for up to 90 days before, 1 for up to 90 days after, and 1 for over 90 days afterwards.
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Administrative Note:
We would very much like to help you with your query, however the thread title does not really convey what your request is about.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not generic.
Please see Forum Rule #1 about proper thread titles and adjust accordingly...
(note: this change is not optional No Help to be offered until this moderation request has been fulfilled)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I see what you are trying to do, and it can be done with conditional formatting. I do not completely understand the sample file, though - there is no today's date entered and I don't quite understand what the relationship is between the main data and the table on the right. A bit more detail is needed, please.
The todays date cell is the current date and as far as the table on the right, I was just trying to invocate what I am trying to achieve with the inspection due date window range, is nothing more than an example.
Hope that helps clarify.
Thanks,
OK - so here are the CF rules you'll need:
Yellow: =$C5-TODAY()>=90
Amber: =$C5=TODAY()
Red: =$C5-TODAY()<=-90
Select C5 and set these rules up, then make sure that the whole range ($C$5:$C$15) is pasted into the Applies To Box.
You might need to tweak the operators to get exactly what you want.
Last edited by AliGW; 03-17-2019 at 11:14 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Unfortunately this did not get me exactly where i need to be. I did modify your rule to use the actual date cell instead of TODAY() so i could see if it was working properly. I have attached my spreadsheet so you can see i am working with multiple vessels with their inspection dates. I chose to include the entire range and no mater which date you use as the current date the conditions executes and it changes all of the inspection date cells which it should not do as they will all have different dates for their inspections. I know i am missing something along the way. Hoping you can point me in the right direction.
Thanks
Ray
The reference to N5 should be relative as in:
Yellow: =N5-$A$3>=90
Amber: =N5=$A$3
Red: =N5-$A$3<=-90
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
I am still having problems with the spreadsheet. I tried as you have indicated and it did not perform as required and I modified the formula a bit to try another approach but it still does not work.
I am not sure what i am doing wrong.
With the conditional formatting in place on all of the cells in column N those dates should remain unchanged except for the color when the date in cell A3 reaches the following criteria.
90 days prior to the due date in column N it should turn yellow.
From the due date in column N to 89 days past that due date in column in the color should change to orange.
When the date in Cell A3 reaches 90 days past the due date in column N the cell should turn red indicating that it is outside of its inspection window.
Hopefully someone can direct me to where i am going wrong with this conditional formatting.
Thank you for your help.
Regards
Ray
Try the following.
For yellow: =AND(N5>=A$3-90,N5<A$3)
For orange: =AND(N5>=A$3,N5<=A$3+89)
For red: =AND(ISNUMBER(N5),N5>=A$3+90)
Let us know if you have any questions.
JeteMc,
I appreciate the help. This is working in the spreadsheet the way I need it to.
Thanks again for your quick solution.
Regards,
Ray
You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks