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

 

[T. Valko]

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

How do you know if it's overdue? I assume you have some type of entry that identifies it as paid (or whatever). So, where is and what is that entry?

 

[robholding]

Thanks for your reply.
It becomes overdue when the last day of the following month passes
Once it is paid, I delete it as I have another way to log the paid invoices.
I just would like to have an 'indicator' for when I need to chase up late payers.

 

[T. Valko]

Thanks for your reply.
It becomes overdue when the last day of the following month passes
Once it is paid, I delete it as I have another way to log the paid invoices.
I just would like to have an 'indicator' for when I need to chase up late payers.

Ok, no problem.

What version of Excel are you using?

 

[robholding]

2010

 

[T. Valko]

2010

Let's assume the invoice date is in cell B2.

Use the "Use a formula to determine which cells to format" option and use this formula:

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

 

[robholding]

Thanks for your quick responce. I tried that formula on the whole column and on an individual cell but it didn't give the desired result.
Please see the attached image. I would like have excel mark the overdue cell. The attached screen shot woud be correct as of todays date (04/08/2012)http://www.facebook.com/photo.php?f...5324.683155330&type=1&relevant_count=1&ref=nf

 

[T. Valko]

Thanks for your quick responce. I tried that formula on the whole column and on an individual cell but it didn't give the desired result.
Please see the attached image. I would like have excel mark the overdue cell. The attached screen shot woud be correct as of todays date (04/08/2012)http://www.facebook.com/photo.php?f...5324.683155330&type=1&relevant_count=1&ref=nf

When I click that link I get this message:

This content is currently unavailable

The page you requested cannot be displayed right now. It may be temporarily unavailable, the link you clicked on may have expired, or you may not have permission to view this page

 

[robholding]

sorry, not having hosting space, I have uploaded it to Facebook
Please try this:

http://www.facebook.com/media/set/?set=a.124813410330.215324.683155330&type=3#!/therobholding

 

[T. Valko]

sorry, not having hosting space, I have uploaded it to Facebook
Please try this:

http://www.facebook.com/media/set/?set=a.124813410330.215324.683155330&type=3#!/therobholding

I get the same message with that link.

Do I have to be a registered member to view that page? If so, I'm not a member of Facebook.