•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Conditional formatting for a specific date range
Our take
Hi all, I’m seeking assistance with applying conditional formatting to highlight cells in green based on a calculated date falling within the defined financial year. I’ve set the financial year parameters in a background sheet called "Settings" and tested my formula, which appears correct: `=AND(G3>Settings!$B$6, G3<Settings!$B$7)`. However, when I implement this rule in the Conditional Formatting Rules Manager for column A, it fails to work. I’d appreciate any insights into what might be going wrong. Thank you!
Hi all.
I'm trying to get a column of cells to fill green if a calculated date in its row falls within the financial year. I've defined this financial year in a background sheet, Settings.
I ran a test column it appears correct, formula
=AND(G3>Settings!$B$6,G3<Settings!$B$7)
Yet when I put this very rule into the Conditional Formatting Rules Manager (to apply to the A column), it doesn't work.
What am I doing wrong?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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]
- 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]
- Trouble creating reference formulas for dates and cell shadingMorning, 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! https://preview.redd.it/8ls6dob2lupg1.png?width=1215&format=png&auto=webp&s=954962fcc6c68b9b890f16f7765d93abafdd08d5 submitted by /u/antagog [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]