Forum Discussion
Insane date formatting problem
Hi! So I'm having an odd issue with Excel. I have a column formatted as dates. I've tried the "Text to columns" solutions for date problems. I've customized the way the date is supposed to display. I still am having this problem and I can't seem to find an online solution to it. I have this spreadsheet made up for paying my bills. It's a very simple spreadsheet, and after I pay a bill, I change the date of the bill to the next payment date for the following month. For the most part, this spreadsheet works great. In just a couple of cases though, the date will NOT format properly, and thereby will not sort. I enter dates by typing "3-13-21" or whatever date. It then always formats it to "03-13-2021" and allows it to be sorted. Today I entered "2-31-21" and it left it exactly that same way, and refuses to sort it. I've tried copying cells that weren't doing that to this cell. I've tried copying empty cells to this cell. I've tried opening brand new workbooks and typing in this number and formatting it from scratch. Nothing I do seems to change this into a recognized date. None of the text to columns solutions I've found. So what am I doing wrong? Its probably something really stupid and simple. But I'm growing frustrated trying to figure it out.
bobthemackerel I believe the problem lies in the fact that you are trying to enter a non-existing date. There is no such date as February 31, 2021. Excel, thus, does not recognise it. Try 2-28-21 and it will work.
8 Replies
bobthemackerel I believe the problem lies in the fact that you are trying to enter a non-existing date. There is no such date as February 31, 2021. Excel, thus, does not recognise it. Try 2-28-21 and it will work.
You got it! You were replying just as I was realizing what a dope I was. : )
Check the attached file, and read the instruction as well comment I've created,, in case this doesn't solves the issue,, share original WB with us.
I appreciate your suggestion, but it turns out that wasn't the problem. If you go into any of the other dates for the other items listed and type in the date in 3-10-21 format it automatically changes it to the correct date formatting. While your solution wasn't actually the issue, it did lead me to think about the problem in a more obvious way. The reason 2-31-21 didn't automatically turn into a date, is because there is no such date. There are at best 29 days in February, but this year only 28. So when I enter 2-28-21 into those slots, it appears just as it should in the date format. I have to assume Excel knows the year and knows that there's only 28 days. I confirmed this by typing in 3-32-21 and it didn't format it as a date either. The solution was so simple and staring me in the face the entire time. When I was changing my due dates, I always just changed the month and left the day the same. Thanks for your assistance though! : )
While entering in cells Excel never checks that how may days falling in month particular or whether moth exceeds 12 or not,,, unless any validation rule has not been applied,,, otherwise I would have get Error in cells B24,, where cell format is mm/dd/yyyy,,,, and the value is 02/31/2021,,the date have been accepted !!.
BUT if you apply this formula =DATE(YEAR(B24),MONTH(B24),DAY(B24)+2) ,, then you get #VALUE! error !!
If you wanna check whether entered date is valid by Month & Days or not the you may use this,, =ISERROR(DATE(DAY(B24),MONTH(B24),YEAR(B24))) you get TURE for invalid and FALSE for valid date.
