Forum Discussion
Insane date formatting problem
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.
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.
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!!