Excel and conditional formatting

  exdragon 13:46 03 May 06
Locked

Hi - I'm keeping a list of invoices prepared, with the dates they are sent out. Can I format the adjacent cells in the next column so that when the due date is reached (date sent plus 30 days), the cell changes colour?

There aren't very many invoices and although I can do something useful in our accounts package, a single spreadsheet with visible reminders would be very handy!

  VoG II 13:54 03 May 06

Select Formula Is and use

=TODAY()-A1>30

Adjust the reference to A1 to the correct column and row. You can then use the Format Painter to apply the format to a range of cells.

  exdragon 13:57 03 May 06

Sorry VoG™ - where's Formula Is?

  VoG II 14:01 03 May 06

Click in the cell you want to format. Format > Conditional Formatting. The dialog that should pop-up has a drop down list that is Cell Value Is as the default. Click the arrow to select Formula Is and type the formula in the box. Then click the Format... button, select the formatting to apply and OK your way out.

  exdragon 14:03 03 May 06

Of course it is! Thank you - I was getting mixed up with the formula bar. Off to work now to impress myself!!

  exdragon 18:15 03 May 06

Why don't I read what you tell me?? I've spent ages trying to work out what's wrong and just realised I'm using Cell value instead of Formula is..

Oh well.

  exdragon 08:16 06 May 06

VoG™'s advice worked perfectly - now, how can I take it one step further? I'd like to be notified when invoices become payable.

I've got a columns for the date I send an invoice, and the adjacent one for the date it's paid. I can apply the formula VoG™ gave me, but the set up is that I have a list of, say, 20 clients but only fill in the invoice date when the service we give them is complete. If I apply the same formula, it colours all the cells which don't already include the invoice date.

The invoice dates aren't entered in sequence, as some services take longer to complete than others.

I think what I'm after is 'colour the cell if it's more than 30 days after this particular date, otherwise leave it uncoloured'. Unless it's easier just to use the formula painter as I go?

VoG™ - where can I get info on basic formulae such as these? I don't want a huge book which tells me everything from how to put a border round cell to how to run the world from a spreadsheet! I think I get a mnetal block about nested formulae, what, if, and and but!

  VoG II 08:30 06 May 06

To prevent the conditional formatting applying when the date cell is empty, modify the formula to

=AND(A1<>"",TODAY()-A1>30)

You could try click here for a start.

  exdragon 08:34 06 May 06

Many thanks - the site looks useful, bookmarked.


Maybe I'll stop bothering you now, but that's not a guarantee!

This thread is now locked and can not be replied to.

Sony Xperia XZ Premium review: Hands-on with the new 4K HDR phone with Motion Eye camera and Snapdr5…

1995-2015: How technology has changed the world in 20 years

Best laptop for design and art 2017: we test Apple, Dell, HP, Lenovo and Microsoft's best models…

CarPlay tips & troubleshooting guide: CarPlay tips & troubleshooting guide: Get the most from…