•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Trouble creating reference formulas for dates and cell shading
Our take
Are you looking to enhance your spreadsheet skills with date management and color-coded cell shading? You're not alone; many users find these tasks challenging. To set up your spreadsheet effectively, you want cell E164 to reflect a date two years after D164, along with a color-coding system: green for the first 10 months, yellow for the following months, and red thereafter. With the right formulas and conditional formatting, you can simplify this process and ensure your data is both informative and visually clear.
Morning,
I could use some help figuring out how to do a thing with dates. YouTube videos and Microsoft Excel help/tips hasn't resulted in a solution. I think I'm close but could also be way off.
I'll try to articulate what I want and what I've done, and I'll add a screenshot.
- What I want:
- Manually enter a date into D164 (aka 3/17/2026)
- E164 to be two years after D164 AND color coded in three ways:
- green from "D164 date" up to 10 months later (aka 3/17/2026 to 1/17/2028)
- yellow starting at that 10 months date until two full years (aka 1/18/2028 to 3/18/2028)
- and red thereafter (aka 3/19/2028 and on)
- What I have/does work:
- E164 formula @ "=IF(D164="","",DATE(YEAR(D164)+2,MONTH(D164),DAY(D164)))"
- E164 conditional formatting (CF) @ highlight cells with red/red if "not blank"
- What I've tried (and I know they're wrong but maybe close?): changing the E164 conditional formatting to the following
- E164 CF @ "=D164=TODAY()+670" set to highlight green
- E164 CF @ "=D164=TODAY()+671" set to highlight yellow
- E164 CF @ "=D164=TODAY()+730" set to highlight red
- What I think I'm missing: do I need to set a cell with the current date for everything else to reference?
Edit to add have/does work: D164 conditional formatting @ "cell does not contain blank value" set to highlight green
Thanks!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Conditional formatting date help neededI've been trying to use conditional formatting to help automate my work spreadsheet and the date formulas truly escape me. I feel like TODAY is a meany who likes to stick their tongue out at you and point for being stupid XD. This is a spreadsheet with a schedule on it. I am trying to get it to automatically grey out the text when the date passes so I can sort and filter by color and always keep the next upcoming appointment slot be top of the list, while still keeping the data in this sheet because another sheet refers to it via XLOOKUP. https://preview.redd.it/z1jqata8w6xg1.png?width=364&format=png&auto=webp&s=d49f71c8de80c402de1af923fc87e3371d606cc8 Here's the formula I'm using =AND($B$2<TODAY(), $D$2<> "") Column D is client names, for privacy purposes I didn't copy that. They end at D11, if it matters. I'm not sure why excel is treating the dates in May as if they are less than today, when they're not. Does anyone have any ideas? submitted by /u/tashykat [link] [comments]
- Conditional Formatting for Dates Missing Numbers (i.e. 3/11/202 instead of 3/11/2026)Does anyone have a formula for formatting dates that are missing numbers? I have created a complex tracker, but it cannot do its job if the user does not enter the date right. I know I cannot stop user error, but I am hoping that it could mitigate some of it if the cell turns bright red when they have done something wrong. I tried and the "ISDATE" function is not working for me. Additionally, I do have text in the document that I do not want formatted so I was playing around with =AND(ISNUMBER(A3),... as the beginning, but could not figure out an end. I also tried typing out an "example date" and having the formula reference that formatting and highlighting the incorrect dates, but I could not get that to work either. Any help is greatly appreciated, please let me know if you have any questions! https://preview.redd.it/r4fuw96cwfvg1.png?width=307&format=png&auto=webp&s=7c8f9db16ceb090d43fc9c8b2b278f32acbb7689 submitted by /u/ClassroomHairy9513 [link] [comments]
- How to make conditional formatting exclude blank cellsMaking a working hours chart but I'm not very well versed in many Excel functions so I've been consulting google results for most, but I don't understand why conditional formatting is marking blank cells green for value >0. The detailed issue is as follows: I have columns for for date (C), start time (D), end time (E), and the following (using row 5 for example): Total time F5 =IF(D5;E5-D5;"") Number format: Time Overtime: G5 =IF(OR(F40="";TIME(8;;)="");"";(F40-TIME(8;;))*24) Number format: General I have set up conditional formatting in column G to color everything that is below 0 red, everything above 0 green, but all the blank cells with above formula applied are colored green too. Cells that equal 0 are left without color, as I intended. My hope is that blank cells also remain white until they recieve a value. Is there a way to achieve that? I appreciate all the help! submitted by /u/ace_098 [link] [comments]
- Conditional formatting with formulasI am trying to create a spreadsheet to track ordered parts and their status, including their requested delivery date. What I'm trying to sort out, and failing at, is setting a conditional format so that when the requested delivery date is less than X days out (say 14 for now), I want it to highlight the cell red so I can check on the status of the order. This is my current rule configuration, which I set by selecting the column so it auto populates as it's filled in. https://preview.redd.it/n4g68xoihzsg1.png?width=409&format=png&auto=webp&s=6715ebba2e873e171fad3b862ff3a7d188b4b3a1 Inconveniently, it's doing the exact opposite of what I want: https://preview.redd.it/h7a7in7ohzsg1.png?width=294&format=png&auto=webp&s=1168efcd78872a5158cd045e6c3ea642c1328152 In this instance, ONLY 4/13 should be highlighted (I'm not worried about the top row at this point, I'll fix that after). I've even tried creating another column to do the math, and use that cell for the formula, and that's not working either. It seems like it should be straight forward, but I apparently lack the formatting skill. submitted by /u/Senior_Cheesecake155 [link] [comments]
Tagged with
#Excel alternatives for data analysis#financial modeling with spreadsheets#Excel compatibility#Excel alternatives#natural language processing for spreadsheets#generative AI for data analysis#rows.com#AI formula generation techniques#formula generator#Excel#date#conditional formatting#formula#D164#E164#color coded#green#yellow#red#MONTH