•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Conditional Formatting for Dates Missing Numbers (i.e. 3/11/202 instead of 3/11/2026)
Our take
If you're facing challenges with date entries in your tracker, you're not alone. Many users struggle with formatting dates correctly, especially when numbers are missing. A bright red cell can serve as a helpful visual cue to signal errors, enhancing user experience. While the "ISDATE" function may not be meeting your needs, exploring alternatives like the combination of "ISNUMBER" and logical conditions can empower your formatting strategy. Let’s collaborate to find an effective formula that addresses these issues while keeping your document's integrity intact.
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!
[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 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]
- 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 based on a different cell's valueI'm fumbling my way through conditional formatting on a tracking sheet I'm creating. I have the status and required delivery formatting the way I want, but now I'm running into an issue where I want it to clear the formatting of the required delivery cell once the status cell is set to "complete" like the bottom row. The status cells are drop down menu if that makes a difference. https://preview.redd.it/0va9fhl9tlvg1.png?width=346&format=png&auto=webp&s=a470a51c639e165a0e4bb41b626d60743cf457c1 I've tried playing around with "IF" formulas, but nothing seems to be happy with what I'm trying. Any thoughts? submitted by /u/Senior_Cheesecake155 [link] [comments]
Tagged with
#Excel alternatives for data analysis#natural language processing for spreadsheets#generative AI for data analysis#AI formula generation techniques#formula generator#rows.com#Excel compatibility#financial modeling with spreadsheets#Excel alternatives#Conditional Formatting#Dates#Missing Numbers#Formula#Excel#ISDATE#ISNUMBER#Tracker#Highlighting#User Error#Example Date