Can't get overdue date conditional formatting to work

Hi Everyone,

Please could I have some assistance with my problem. I have attached the spreadsheet I am having trouble with. I am trying to set it up so that when an invoice is logged the invoice number turns orange. When the invoice is paid, it turns green. I have managed these two so far. Now I need a third function where if payment is not received by the date specified the invoice cell must turn red. I tried the formula of =$C<TODAY() but for some reason it will not change the cell colour. Regardless if it is overdue or not yet.

I've been searching high and low for days to resolve this. An explanation to the solution will be appreciated so I can learn from this problem.

Thank you.
Warren

Hi Warren

I've come across this type of problem before
CF doesn't seem to like dates or times
fix for this has been to reference cells that don't have any date or time formatting

using 'helper' columns seems to be a work round

Please Login or Register  to view this content.

@Ford - how come then, when using formula in CF at cells that have dates gives a different outcome to when it references cells that just have Value(date), as in the file i uploaded

one way of getting rid of those additional columns would be to apply this formula to CF

Please Login or Register  to view this content.

Hi ThirtyTwo,

This worked like a charm. Thank you very much for the help and the speedy response! One day I'll be a wiz like you guys.

Cheers

@FDibbins,

I see what you mean. If I enter the date as "7 June 2014" and have the format set to short date that seems to also do the trick. It obviously was not seeing the date I entered as a "date".

Again thank you to all for helping out so quickly and responding to my question.

Cheers