Conditional Highlighting for Quarterly and Semiannual Tasks

Hi all,

At work we have to complete a certain task quarterly for some clients and semiannually for others. I enter the date of the task completed in Column C (see sample worksheet).

I'd like to conditionally highlight the cell in Column C to remind me if the quarter (for Type Q clients) or semiannual period (i.e., June 30 or Dec 31; for Type S clients) is within 15 calendar days and I have not done the task yet in the current quarter or semiannual period (meaning either the cell is blank or has a date from a previous quarter or semiannual period).

I had been offered a previous solution elsewhere that I can't remember off the top of my head. I was trying to use two formulas (the one below for quarterly reminders) and then changing all the instances of "3" to "6" for semiannual reminders, but the formulas no longer seem to highlight. Also, I had trouble when I was trying to add another "and" to distinguish between Type S and Q clients:

Please Login or Register  to view this content.

(spaces added around greater/less than symbols to avoid Sucuri firewall block)

I am open to doing this in two formulas if it makes it less complex. Thank you for any ideas!

April

ps full disclosure...posted at Mr. Excel Forum ( http://www.mrexcel.com/board/threads...-tasks.1175623 ) 4 days ago but no response, so I'm trying here.

You can post a URL by breaking it into sections - please do this with the link to the cross post. Thanks.

Just looking at your workbook - do you really mean that 30 March should be the end of the first quarter? March has 31 days ... ???

Thanks - I've converted it to a link for you.

By the way, they have the same disclosure rule over there, so you'll need to post a link back to here on Mr Excel.

Originally Posted by AliGW

Thanks - I've converted it to a link for you.

Thank you! (and I added the link to the MrExcel thread as you suggested)

Can you add a few more rows of dummy data and manually colour code where appropriate to show exactly what you want?