Insane date formatting problem

[bobthemackerel]

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.  

[#2]

@bobthemackerel 

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.

[#3 · Accepted solution]

@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.

[#4]

@Rajesh_Sinha 

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! : ) 

[#5]

@Riny_van_Eekelen 

You got it!  You were replying just as I was realizing what a dope I was. : ) 

[#6]

@bobthemackerel 

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.

[#7]

@Rajesh_Sinha Not sure what you are getting at, but your "date" in B24 is, in fact, a text. You didn't get an error because "02/31/2021" is a valid text, just like "ABC123". Enter that in B24 and you won't get an error either, despite the fact that you formatted the cell as a date.

The #VALUE! error occurs because you can't use a date function (like YEAR, MONTH, DAY) on a cell that is not a date. As you know, dates are stored as numbers. Try this:

=ISNUMBER(B24)

and you will get FALSE. B24, therefore, does not contain a date. And as explained earlier, the 31st of February is never going to be recognised as a date. No matter how you format it.

[#8]

@Riny_van_Eekelen 

Check the attached file & cell B25, B25 the date value are not TEXT,,, since Right aligned, as well has CUSTOM format.

What I was trying to say while entering Excel doesn't check whether Data value is valid or not unless any validation rule has not been applied,, but as soon formula applied the Excel checks the validity.

In B24 if you enter 02/29/2021 then also get the #VALUE! error ,, coz this year Feb has 28 days only, and try to enter 02/28/2021 or any valid date, then in C24 you find formula is working, and the same U may try in B25 also!!

[#9]

@Rajesh_Sinha ???