Conditional Formatting to show age of invoice?
Hi
I'm looking to add some conditional formatting that will highlight a row when an invoice becomes 30 days old, 60 days old and greater than 90 days old.
Can anybody advise me if this is possible and if so, the formula I need to add?
Many thanks in advance
Select the range of data you want to format and use the following for each condition
=and(today()-$A1>=30,today()-$A1<59)
=(today()-$A1>=60,today()-$A1<89)
=(today()-$A1>=90)
Hopefully it should work
Last edited by phoenixphi; 06-08-2012 at 05:53 AM.
@phoenixphi
If you use that option you need to use the AND function likeYour solution can be shortened going the other way round
Please Login or Register to view this content.
CF1CF2Please Login or Register to view this content.CF3Please Login or Register to view this content.Please Login or Register to view this content.
Please see attached file for your reference. You did not upload your file, so it may not meet your exact expectation.
Thanks everyone.
I'm not sure how to upload a file sorry!
Is it possible to highlight the whole row rather than just the cell when the conditions are met? If so, would it be possible for another file to be uploaded with the formula on?
another method is highlight row 1 and formula 1 =IF($A1<=TODAY()-90,TRUE,FALSE) format colour
formula 2 =IF($A1<=TODAY()-60,TRUE,FALSE) format colour
formula 3 =IF($A1<=TODAY()-30,TRUE,FALSE) format colour
copy down as needed
Last edited by grizzly6969; 06-08-2012 at 06:33 AM.
@grizzly
The use of If, TRUE FALSE is implicit in CF and therefore unnecessary
Originally Posted by leven
As Phoenix described, you should select your entire range. So, if you need to format range A1:C5, highlight it entirely starting in A1 and enter the suggested formulas - The $ before the A in $A1 is the important part
This is probably a daft question but does the $A1 refer to the cell that the date of invoice would be in?
Indeed - But you can replace it with any other reference
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks