•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Conditional formatting date help needed
Our take
Are you struggling with conditional formatting in your work spreadsheet? You're not alone! Many users find it challenging, especially when trying to automate date-related tasks. In your case, you want to grey out text for past appointments to prioritize upcoming slots, while ensuring data integrity for XLOOKUP references. It sounds like your formula, `=AND($B$2<TODAY(), $D$2<>"")`, is close but may have some quirks with date recognition. Let’s explore how to refine your approach for clearer sorting and filtering.
I'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.
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?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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 checkboxes and datesI need to highlight a cell (Column "I") that has a checkbox if it is unchecked and a date in a separate column (Column "D") occurs in the next 3 days. https://preview.redd.it/vrpvkbcaxwzg1.png?width=464&format=png&auto=webp&s=40bcf88095d42cc9a11c8edd7ae76375e7a703ed Thank you submitted by /u/_Rocksol [link] [comments]
- Conditional Formatting within a date rangeI am building a heat map for missing timecard data by weeks. The weeks missing data are in column C->Z. Cell C2 is Jan 2nd, D2 is January 9th, etc I am trying to apply conditional formatting to the data in cells C3-Z3. But only if the date in row 2 falls within a separate date range in columns A/B. For example if someone was scheduled to work only in February, cell A3 would be Feb 1st and cell B3 would be Feb 28th. I want apply conditional formatting for the C->Z data to the right for February but not Jan/March/etc. Is it possible to do that? submitted by /u/Anneisabitch [link] [comments]
- Stop using ungodly INDEX math to flatten 2D schedules. TOCOL() + FILTER() is all you need.This comes up constantly. Someone gets handed a resource tracker or a system export where tasks are split across "Morning Task" and "Afternoon Task" columns, and they need a flat list to dump into a Pivot Table. Simple enough ask. The fun part? Half these exports don't even leave cells blank - they write out [empty] as literal text, so any trick that relies on detecting blank cells just falls flat. And on top of that there's usually a Status column you need to drag along, but only once per person - not stamped next to every single task row like a broken rubber stamp. Old solution was some deeply cursed nested INDEX/ROW formula that nobody could read six months later. If you're still doing that, please stop. On Office 365 you can handle the whole thing in one shot: =LET(data, A2:C11, status, D2:D11, col_data, TOCOL(data), col_status, TOCOL(IF(SEQUENCE(1,COLUMNS(data))=1, status, "")), FILTER(HSTACK(col_data, col_status), col_data<>"[empty]")) TOCOL flattens the grid, the IF/SEQUENCE combo makes sure the status only shows up next to the name and not repeated under every task, HSTACK glues the two columns together, and FILTER kills all the [empty] noise. Keep your ranges the same size throughout or you'll get a #VALUE! staring back at you. These dynamic array functions honestly flew under the radar for a lot of people who don't spend their weekends reading Excel update logs. Hope it saves someone a headache. Note; for Excel 2019 and 2021 you can use power query. However in the 2021 version you can use the filter and sequence function. In 2021 version; the formulas are; =FILTER(INDEX(A2:C11,MOD(SEQUENCE(30)-1,10)+1,INT((SEQUENCE(30)-1)/10)+1),INDEX(A2:C11,MOD(SEQUENCE(30)-1,10)+1,INT((SEQUENCE(30)-1)/10)+1)<>"[empty]") and =FILTER(IF(INT((SEQUENCE(30)-1)/10)+1=1,INDEX(D2:D11,MOD(SEQUENCE(30)-1,10)+1),""),INDEX(A2:C11,MOD(SEQUENCE(30)-1,10)+1,INT((SEQUENCE(30)-1)/10)+1)<>"[empty]") one thank go at user for pointing it out to me Excel_User_1977 submitted by /u/Good-Willingness2234 [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#Excel compatibility#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#automation in spreadsheet workflows#no-code spreadsheet solutions#AI-powered spreadsheet#Excel alternatives#spreadsheet API integration#rows.com#AI formula generation techniques#big data management in spreadsheets#conversational data analysis