Rick_Penney
asked on
Conditional formatting in Excel for dates older than 2 weeks
Hi, i have an Excel document that is used to record dates that of maintenance, backups etc.
I would like to apply conditional formatting to cells that contain a date that is older than then the "Todays" date by 2 weeks plus by highlighting those cells in a different colour.
I can see an option where you can manually enter "Format cells that are Greater than date", but i dont want to have to keep manually changing this date, i guess there is an option for it to look at the PC's current date and highlight any cell that is older by more than 2 weeks
regards
Rick
I would like to apply conditional formatting to cells that contain a date that is older than then the "Todays" date by 2 weeks plus by highlighting those cells in a different colour.
I can see an option where you can manually enter "Format cells that are Greater than date", but i dont want to have to keep manually changing this date, i guess there is an option for it to look at the PC's current date and highlight any cell that is older by more than 2 weeks
regards
Rick
If A2 and below also have dates, you can then copy A1 and use Paste Special > Formats to copy the same format to the cells below. Since the conditional formula contains a relative reference, the next row down will check A2, the next one A3, and so on.
cheers, teylyn
cheers, teylyn
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi
many thanks to both of you for your quick replies and help, sorry teylyn , i couldn't get your formula to work, i wasn't adding any spaces so maybe that was why.
Thank you akoster, your formula works perfectly. is it possible to apply this to multiple cells in one go rather than doing this for each cell individually?
thank you
many thanks to both of you for your quick replies and help, sorry teylyn , i couldn't get your formula to work, i wasn't adding any spaces so maybe that was why.
Thank you akoster, your formula works perfectly. is it possible to apply this to multiple cells in one go rather than doing this for each cell individually?
thank you
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok, thanks for the explanation, i did put (contain a date that is older than), anyway,
i'll increase the points to 500 and split it between you.
hope thats ok
regards
Rick
i'll increase the points to 500 and split it between you.
hope thats ok
regards
Rick
ASKER
Many thanks for the speed of your help
You're welcome...
You can select the complete column A (or a range from A1 to let's say A22), select conditional formatting, enter the formula, and the complete selection will be conditionally formatted on each different cell.
So it's not like the whole column will be colored when cell A1 changes to a date in last year.
You can select the complete column A (or a range from A1 to let's say A22), select conditional formatting, enter the formula, and the complete selection will be conditionally formatted on each different cell.
So it's not like the whole column will be colored when cell A1 changes to a date in last year.
if A1 has a date, then select A1 and create a conditional format with New Rule > Use a Formula to determine ...
Enter this formula
=A1>=today()+14
Select a format and hit OK.
cheers, teylyn