I have a spreadsheet that when I enter a date of an invoice issued for example 1st October 2019 in cell G68, cell L68 has the formula '=G68+30' which shows a date 30 days (31st October 2019) after the invoice has been issued. What I would like to do is for the cell background to change to red if the current date is greater than 30 days. So if the actual date is 1st November 2019 the cell background would be red making it easier to determine which invoices need chasing.
Thanks.
How about
=L68< TODAY()-30
Expanding on that
you might need
=AND(L68<>"",L68< TODAY()-30)
if you have (as yet) unentered dates
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Thank you both. I pasted both in and formatted the cell to turn red but it didn't work. I changed both formula to
=L68< TODAY()-30
=L68> TODAY()-30
and
=AND(L68<>"",L68< TODAY()-30)
=AND(L68<>"",L68> TODAY()-30)
And where the date in L68 was 28 September 2019 which is 30 days greater than the date in cell G68 (29 August 2019) L68 turned red, Great. but when I copied the formula and formatting to L69
Date in L69 was 17 October 2019 which is 30 days greater the date in cell G69 (17 September 2019) L69 turned red when the actual date is only 3 October 2019. So it shouldn't turn red until 17 October 2019.
Rough guess: Your dates are not proper Excel dates but text.
The first cell G68 and L68 are text entered dates but cells L68 and L69 has the following formula in it.
=G68+30
=G69+30
I may help to avoid confusion if you could upload a sample worksheet that displays manually highlighted examples of all of the possible scenarios.
To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
I take it that's a typo.Originally Posted by Novice999
L68 cant have a manually entered value and a formula in the same cell.
More importantly, what is a "text entered date"?
The cell is either text or a number (date).
Test using a blank cell with either ISTEXT() or ISNUMBER().
Or better still, follow JeteMC's request and upload the file.
I have attached a slimmed down version of the excel sheet. Hopefully it is clearer as to what I am trying to achieve. Thank you for your help on this.
Try this
Select the range to highlight, e.g. L20:L23
Conditional Formatting
New Rule
Use a formula to determine...
4 formulas required, one for each colour
=K20="PAID"
format as green
=AND(L20>0,L20-TODAY()>=0,L20-TODAY()<30)
format as blue
=AND(L20>0,L20-TODAY()>30,L20-TODAY()<60)
format as yellow
=AND(L20>0,L20-TODAY()>60)
format as red
Make sure the top formula is the green one
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks