Conditional formatting with date formulas?

[Meagan29]

Hi this is my first post and hopefully someone can help.

I have a spreadsheet to manage tasks for a large team. I have Frequency in column D and Date in column F. The frequency is a data validation drop down list that ranges from Daily, weekly, fortnightly, monthly, random, N/A. The team enters the date they last reviewed a task in column F, that review is indicted when it needs to be done in the Frequency column D.

So what I’m wanting to do is if columns in D state “monthly” then for the dates in column F to be highlighted if they are older than 1 month. If D states weekly then F highlighted if older than 7 days. So on and so forth for the following.

• Daily = 1 day
• Weekly = 7 days
• Fortnightly = 14 days
• Monthly = 30 days
• Random = 90 days

However, the frequency could possibly change from say weekly to monthly. So I want to be able to select the new frequency and it automatically pick up the new date range.

Is this possible??

Thanks
Meagan

[protonLeah]

(How to) Attach a Workbook
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

[David A Coop]

Hi Meagan,

Welcome to the forum.

I have a solution for you!

Meagan - Cond Format example.xlsx

It may need a bit of refinement, depending on how many cols you want to format, and where your list of Daily, Weekly etc is. (I recommend having it on your workbook, and name the range as I have ("period" in my example).

The conditional format uses MATCH and CHOOSE to decide which cells to highlight.
=F1>(NOW()+CHOOSE(MATCH($D$1,$A$1:$A$5,0),1,7,14,30,90))

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".