Date Formatting

Question

I am trying to enter a date of 2/31 in the cell. However when I tab out of the cell, it changes the date to 2/1. Why is is not retaining the 2/31?

Answer 1

Excel is correcting it because February 31 does not exist as a valid calendar date. February has 28 days in a normal year or 29 days in a leap year. When you enter 2/31, Excel interprets it as a date calculation rather than preserving it as literal text. It effectively rolls the excess days into the following month, which is why you may see 3/3 in some circumstances. If you are seeing 2/1, Excel may instead be interpreting your entry according to the cell's existing date/number formatting or another date-related setting.

If you specifically need the cell to display 2/31 even though it is not a valid date, enter it as text by using an apostrophe: '2/31. The apostrophe will not be displayed in the cell. Alternatively, format the cell as Text before entering 2/31. In either case, Excel will retain 2/31 exactly as entered, but it will no longer be treated as an actual date for date calculations.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 2

Thank you Marcin,

Your solution worked like a charm. I will remember it for future use should the situation arise.