Function Help - Forecast Date past due, adjacent cell is blank. Needto highlight the blank cell.

A

Andrea

Hi,
First post here...

I am working on some Project Forecasting. Need a quick way to spot check and fix any past due items. There HAS to be an easier way than sorting the Column A dates and then filtering adjacent cells to show only Blanks.

Is there a formula or conditional format that will highlight a cell (either A or B, or both) if the following is true: 1)It past due or due today AND 2) the adjacent cell in Column B is blank - meaning item has not yet been completed.

Example:

Column A Column B
11/22/2013 (blank cell)

This pattern continues repeatedly on my spreadsheet. (i.e. Column A: Item 1 Forecast Date, Column B: Item 1 Actual Date, Column C: Item 2 Forecast Date, Column D: Item 2 Actual date, etc...)

Hopefully there is a formula I can enter as a 'New Rule' under the Conditional Formatting menu.

Thanks much!
 
M

MyVeryOwnSelf

Is there a formula or conditional format that will highlight a cell (either A
or B, or both) if the following is true: 1)It past due or due today AND 2)
the adjacent cell in Column B is blank - meaning item has not yet been
completed.

Example:

Column A Column B
11/22/2013 (blank cell)

This pattern continues repeatedly on my spreadsheet. (i.e. Column A: Item 1
Forecast Date, Column B: Item 1 Actual Date, Column C: Item 2 Forecast Date,
Column D: Item 2 Actual date, etc...)

Hopefully there is a formula I can enter as a 'New Rule' under the
Conditional Formatting menu.

With column B selected, try this as a conditional format formula to determine which cells to highlight:
=AND(B1="", ISNUMBER(A1), TODAY()>A1)
This should highlight column B as desired. The ISNUMBER part is to ignore rows past the bottom of the list.

The format-pasting operation can paste column B's format onto columns D, F, H, ...

Hope this helps getting started.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top