•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Conditional formatting for dates
Our take
Conditional formatting for dates can enhance your spreadsheet's functionality by visually signaling important deadlines. To achieve automatic color changes for application dates, set up rules that turn dates orange after 30 days and red after an additional 7 days. Importantly, if the "completed" column is marked with "D," these color changes should not apply. This approach allows for clear visibility of pending tasks while acknowledging completed ones. Let’s explore how to configure these settings effectively for optimal data management and clarity.
How would I achieve automatic colour changing dates with exceptions.
For example I need to be able to put in any date for applications (so they don't exist yet.) and then after 30 days i need it to go orange and then from that 30 days another 7 days it goes red but if the completed column is checked i need the conditional formatting to be invalid as it wont be necessary for the colours to change.
How do i achieve this?
column - date application received (23/02/2026) turn orange at (23/03/2026) turn red at (30/03/2026) stays white or turns white is column competed has "D"
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Conditional formatting for future datesPlease let me know if there's a better place for this question, I'm new to reddit. I have to keep track of dozens of people various certifications. The date inputted is the date they got it. I want to format it so it's green unless: In 9 months from today's date it turns yellow In 11 months from todays date it turns red I'd like to use the edate feature to account for monthly shifts, but the only formatting I've done were on actual expiration dates (using the greater than/less than feature), not future ones. Any suggestions or videos that would help? submitted by /u/Altriss1 [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]
- Conditional formatting for 30,60,90 a year from due dateHello, please forgive my very basic excel knowledge. I looked through Google and YouTube and still couldn’t figured out. I want to create a conditional formatting rule where it highlights 30,60,90 days prior to a due date. Let’s say the date is 5 March 2026, I want to set a ‘reminder’ for a year from now. I want to set a reminder for 30 days from 5 March 2027, 60 days from 5 March 2027, and 90 days from 5 March 2027. And if it’s past 5 March 2027, then I want to format it to be red. I tried googling it and everything I found show me how to create conditional formatting rules that is 30 days from today. Like = today()+30 But that is not what I want though. I want to know 30 days from the date year from the date I have In the cell. Please help. submitted by /u/Ashe225 [link] [comments]
- How to use TODAY() to conditionally format due dates?I am a complete noob at excel so my apologies if this is very easy. Essentially, I am trying to make a column where I write the last date I received records. I’m supposed to get updated records at least once every 90 days, but frequently they’re late. I want the cell to turn red if the date in it is older than 90 days. The column is K and the first cell with info is K2. I tried =K2<TODAY()-90 and =$K$2<TODAY()-90, but the test dates I tried either don’t turn red when they should or *all* of them turn red even if left blank. What formula should I use? submitted by /u/boopbaboop [link] [comments]
Tagged with
#Excel alternatives for data analysis#natural language processing for spreadsheets#generative AI for data analysis#rows.com#machine learning in spreadsheet applications#Excel compatibility#cloud-based spreadsheet applications#financial modeling with spreadsheets#Excel alternatives#conditional formatting#dates#automatic colour changing#exceptions#completed column#applications#turn orange#turn red#date application received#invalid#30 days