Conditional Formatting for "NET Monthly Account"

[robholding]

Hi all,

I was hoping someone may be able to help me.

I have an excel sheet where I log all my sent invoices. One of the columns shows the day that the invoice was created.
I would like to use conditional formatting to 'hi-light' an invoice (date) that is overdue by net one month (Overdue when the end of the following month is reached)
For example, If I send an invoice on the 1st of January, the invoice is due to be paid by the last day of the following month i.e. 29th February.
The same would be true if the invoice date was the 31st Jan, the invoice would still be over due on the last day of the following month.

Does anyone know the formula for this? I am looking to apply the formatting to the whole of 1 column.

Grrr - driving me crazy lol

Many thanks

 

[robholding]

Sorry...

 

[T. Valko]

Sorry...

If I understand what you want to do then those 2 cells should be highlighted.

The overdue dates would be:

B2: 6/15/2012 = overdue on or after 7/31/2012
B3: 6/25/2012 = overdue on or after 7/31/2012

Isn't that what you wanted?

 

[robholding]

Exactly that!

 

[T. Valko]

Exactly that!

Ok, is it not working?

 

[robholding]

I just tried it again...I was applying it by mistake to the column and not just 1 cell.
To apply the formula to the whole column, would I copy and paste the formatting or the formula?
Is there a quicker way to apply it to the whole column?

 

[T. Valko]

I just tried it again...I was applying it by mistake to the column and not just 1 cell.
To apply the formula to the whole column, would I copy and paste the formatting or the formula?
Is there a quicker way to apply it to the whole column?

You can apply the formatting all in one step.

Let's assume the range to format is B2:B20.

Select the *entire* range B2:B20 starting from cell B2. Cell B2 will be the active cell. The active cell is the one cell in the selected range that is not shaded. The formula will be relative to the active cell.

Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format

Enter this formula in the box below:

=TODAY()>=EOMONTH(B2,1)

Click the Format button
Select the desired style(s)
OK out

 

[robholding]

Thank you for the formula, It's working!
Rather than selecting the entire range, I changed the "Applies to" field (in the Conditional Formatting Rules Manager to $B:$B so the rule will be applied to any new entries.
Thank you VERY much for all your help!!!

 

[T. Valko]

Thank you for the formula, It's working!
Rather than selecting the entire range, I changed the "Applies to" field (in the Conditional Formatting Rules Manager to $B:$B so the rule will be applied to any new entries.
Thank you VERY much for all your help!!!

You're welcome. Thanks for the feedback!