Excel - highlighting line of data within a certain date range

Question

I have a spreadsheet which I record things we have on hire. I need a way to highlight long term hire nearing its end date so I can check if it needs extending or collecting. We have the end date in a column so when it is say 3-4 days before the end date the line would turn green or the text would change colour so we can spot what needs to be done easily. We thought about filtering but this only works day by day and not in a specific range. It is a shared workbook so not sure if that affects anything. So if the end date is 5th on the 1st the line would turn orange say, and stay that way until the date is changed.

We don’t put end date on for every hire just long term but would apply to the whole sheet so wouldn’t want errors if the cell is blank.

Help please :)

Answer 1

Dear Cara,

Welcome to the forum here.

Regarding the requirement, if it is convenient, could you upload a full screenshot of the example sheet so that we can clarify the example and try to provide a solution?

Note: Remove any private information before uploading the screenshots.

Thanks for your effort and time.

Cliff

Answer 2

Hi Cara

Please, follow the steps in the picture below

And apply the following CF formulas

Black = Blacklisted/Expired =DAYS($C2,TODAY())<0

Red = 3 days to the deadline =AND(DAYS($C2,TODAY())>=0,DAYS($C2,TODAY())<=3)

Orange = 7 days to the deadline =AND(DAYS($C2,TODAY())>3,DAYS($C2,TODAY())<=7)

Yellow = 15 days to the deadline =AND(DAYS($C2,TODAY())>7,DAYS($C2,TODAY())<=15)

Note:

Adapt the ranges, colors, and criteria as per your scenario and needs.

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 3

Hi Cara. I am an Excel user like you.

Apply Conditional Formatting to all of the cells you want to change color by selecting them all as in A1:D13 below. Open the Conditional Formatting dialogue and choose "Use a formula...". In the formula line enter this formula and then choose the formatting you want:

=IF(AND($A1>0,$A1-TODAY()<5),TRUE,FALSE)

You will need to adjust the two A1 references in the formula to the correct cell where you have your end date. Be sure that the first line of the range you select matches the row of the cell you refer to in the formula. In other words, if your first end date is in row 2 begin your selection also in row 2. This will highlight lines where your end date is 4 days or less from the current day's date. If you want more or less, adjust the <5 accordingly.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M