Feb 23 2020 12:05 PM - edited Feb 23 2020 12:09 PM
Feb 23 2020 12:05 PM - edited Feb 23 2020 12:09 PM
I'm using Office 365 and I have a column of dates that are sorted in ascending order (recent date on top). In Conditional Formatting\Highlight Cell Rules\A Date Occurring section, there are three provided options to format Weeks: "Last week", "This week", and "Next week". I've successfully utilized all three of those and applied a different fill color for each week. It's exactly what I need. I would also now like to do the same for weeks occurring after "Next week". For example, 2 weeks in the future, 3 weeks in the future, 4 weeks in the future. I've found lots of documentation that provides formulas for # days in the future, but that doesn't do what I need because # days in the future doesn't take into account the actual week. I need the formatting to recognize calendar weeks the same way that the provided options "Last week", "This week", and "Next week" do. Is this possible? A Microsoft Support rep just told me via Chat Client it's not. I'm having a hard time believing that's true. Thanks in advance.
Feb 23 2020 01:27 PM
If I were tackling this myself, I think I'd work out some "helper cells" or a "helper table" off in an obscure part of my workbook, where I'd first determine what range of dates falls in each of those weeks. One of those helper cells would be a simple "Y" for "2 weeks out" or "3 weeks out" etc.
And then refer to that cell (those cells) in the conditional formatting dialog box.
Feb 23 2020 03:19 PMSolution
conditional formatting rule:
X=number of weeks
Feb 25 2020 06:46 AM
@Patrick2788Thanks much for this! It *almost* works. I added the formula four times exactly as you typed it, substituting 1 thru 4 for X to shade the next 4 weeks.
So today is 02-25-20 (for those reading the post in the future). The new formulas shade in variants of gray that increase with each week for the next 1-4 weeks. I've attached a picture of what the result is. Sort of weird that the formula(s) see the same date, 03-12-20, in two different weeks: Week 2 and Week 3. Thanks again for your help. I think your formula may be the answer, but something needs to be tweaked; I'm just not sure what.
Ideally, the dates would be shaded in variant shades of gray as follows (weekend days won't appear as they are not used in this application). NOTE: Red is last week and Peach is this week (those were already done with the conditional formatting options provided by excel).
Week 1: 03-02 thru 03-06
Week 2: 03-09 thru 03-13
Week 3: 03-16 thru 03-20
Week 4: 03-23 thru 03-27
Feb 25 2020 10:29 AM
@Patrick2788 Thanks again! So on a hunch, I checked out the order of my formulas. I had added Week 4's formula *prior* to Week 3's formula which made Week 3 the last in the list and forgot to reorder them in correct order (Week 1, Week 2, Week 3, Week 4). After I reordered the formulas, it worked like a charm, so apparently the order makes a difference. Thank you so much!!! It makes me sad that when one contacts the sanctioned Microsoft Excel Support Experts for a paid account via the Chat Tool, you're told it can't be done. I *knew* it had to be possible as I couldn't have been the only person on the planet that needed to see dates in the future in relation to how many weeks out they are. Much respect to you @Patrick2788!
Feb 25 2020 12:09 PM
Most things are possible in Excel with some creativity. I'm surprised you were told no.
Feb 25 2020 12:53 PM
@Patrick2788 Right?!? Me too! Especially now that you provided me the function name and I Googled it. There's info EVERYWHERE about it. I don't think it was me because I was extremely specific about what I needed, basing my description on the current allowable options (Last Week, This Week, Next Week). Maybe it was a language barrier thing.... Can't thank you enough though...You rock!
Feb 25 2020 01:50 PM - edited Feb 25 2020 01:51 PM
@mathetesThank you so much for your suggestion. I was able to do it with a single formula (see above).
Jan 29 2021 04:44 PM - edited Jan 29 2021 04:45 PM
@SWB_MTC I tried your exact formula, and when I enter a date in a cell, the cell above it gets highlighted, not the cell I need. What is the cause of that?
Do I need to change "A2" to which column my cell is on, for example "M"?
Jan 30 2021 01:23 AM
In conditional formatting rule formula for the reference you shall use first cell of the range to which the rule is applied.
Jan 30 2021 01:31 PM
@MSM182 Correct. You have to use the Column letter for the specific column you're working in. I've attached a copy of the original spreadsheet that @Patrick2788 was so kind to create for me and I added my own Column for you to see examples of Conditional Formatting with an Excel provided template date (like Today and Next Week) along with following weeks using the formula @Patrick2788 provided. My column has Today, Next Week, and then Weeks 2-8 in the future. I included a Color Legend because that's helpful to see at-a-glance. NOTE: @Patrick2788 provided a cell range in his example for the "Applies to" area. However, I used the entire Column (in my case "=$D:$D") in the "Applies to" area. Another difference: His formula references A2, which has a date entered. Conversely, my formula references D1, which is just text. This proves that you don't necessarily have to have a Date entered in your reference cell. It works both ways.