Cell Formatting, Color by date closeness to present date.
Hi!
I was asked to create a form that would color a date cell based on its proximity to the present (date of the Sheet when opened). the problem is, I don't know how to do this. I can format the cell with the preset formats, but it only lets you make one rule per cell when I need 4.
Requirements Example:
If the date in the cell is more than 1 month away, the cell's background will be green
If the date in the cell is the present month, the cell's background will be yellow,
If the date in the cell is within a week of the present date opened, the cell's background needs to be orange
and if the date in the cell is the day of, or in the past from, the current date (that the sheet is opened), then the background of the cell will be in red.
I really appreciate any help that anyone can give!
Posted Jun 21, 2018, 7:16 PM UTC
That’s just conditional formatting that you’re looking for. You can subtract dates from each other and Excel will return the difference. So you can use formulas to subtract the date from TODAY() (which is a formula that returns the current day’s date) to get the time difference.
As for using conditional formatting, rather than reinvent the wheel, here’s a guide:
https://support.office.com/en-us/article/use-formulas-with-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f
Thank you! I'll try this out tomorrow and let you know how it goes :)
I have a little more time now, so just to give a bit more guidance, if for example your dates start in Cell A2 and continue down, you’ll have four conditional format rules (“Use a formula to determine which cells to format”). Highlight from A2 to the end of where you want to apply the conditional formats and the open the conditional formatting window and put in the following rules:
=A2-TODAY()<1red=A2-TODAY()<8orange=A2-TODAY()<31yellow=A2-TODAY()>=31GreenI recommend this order so it doesn’t matter which option you have regarding stop checking more rules if true. Note that I didn’t include any absolute references ($ signs). If you only have dates in one column, you could change “A2” to “$A2”.
hi!
Firstly let me thank you for all your help, I greatly appreciate it. I just have the tiniest levels of understanding about excel so I'm sure I'm doing something wrong here, but when I enter all the formula The cells all go to either not filled, or the wrong color
Here is the link of what I have done so far. I know the cell number under "applies to" is a different number than the original formula, but that is because i was trying to paste the format into another cell. Unfortunately the column for the dates does not only contain dates
https://imgur.com/a/we84jzw
If the cells this applies to are intermittent, you can just set it up once on the first cell and copy the formatting to the other cells like you’re doing. To fix it, in the first instance make sure the cell reference in the formula agrees with the cell it applies to, and get rid of the “$” from the formula.
So when you look at the formula in the rule when you have cell E4 selected, the formulas should all start with:
And NOT