Excel changes dates to numbers automatically

Question

Sometimes Excel changes the dates eg 01/19/2021 to a number eg 44215. 

I can't change the number back to a date.  I already tried undo, and it didn't work.

In the dialog box it still displays the date as 01/19/2021 format, even though in the cell it shows 44215.

Please, I need help!! 

How do I reverse this and get the cell value back to a date? 

Thank you. 

Irene

Answer 1

44215 looks like a date serial number, in "general" data type format. Simple fix

.

Select the cell, or whole column.

In the Home tab > Number group > Type Dropdown

Select one of the Date types from dropdown

After you redefine the "General" number to any date type you can use a custom data format to display the date in any format you wish

!   CustomDate / Time format codesEvery “Date” has implicit time attached, and every “Time” has implicit date attached.
Date CodesThe letters d, m, and y are used to represent days, months, and years in Excel date formats.
Date Mask Displays ****.  d                days (1-31) without a leading zero
.  dd              days (01-31) with a leading zero
.  ddd            days (Sun-Sat) using 3-letter abbreviations
.  dddd          days (Sunday-Saturday) full day spelled out
.  m               months (1-12) without a leading zero
.  mm            months (01-12) with a leading zero
.  mmm         months (Jan-Dec) using 3-letter abbreviations
.  mmmm      months (January-December) full month spelled out
.  mmmmm   months (J-D) the first letter of the month
.  yy               years (00-99) last two-digits of year
.  yyyy            years (1900-9999) all four-digits of the year

Personally, I avoid the mmmmm format. How can you (or anyone else reading your worksheet) distinguish between January, June, and July? Between March and May? Or April and August?

Time CodesThe letters h, m, and s are used to represent hours, minutes, and seconds in Excel time formats. Additionally, you can specify elapsed time and AM or PM in a custom format -
Time Mask Displays ****.  h                       hours (0-23)   without a leading zero
.  hh                     hours (00-23) with a leading zero
.  m                       minutes (0-59)   without a leading zero
.  mm                    minutes (00-59) with a leading zero
.  s                        seconds (0-59)  without a leading zero
.  ss                      seconds (00-59) with a leading zero
.  [h]:mm               elapsed time in hours      (such as 28:30)
.  [mm]:ss             elapsed time in minutes   (such as 65:20)
.  [ss]                     elapsed time in seconds (such as 90)
.  ss.000                Decimal seconds to thousands
.  [h]:mm:ss.000    Decimal Seconds to thousands
.  h:mm AM/PM     hours (such as 6:00 AM or 6:45 PM)
.  h:mm A/P           hours (such as 6:00 A    or 6:45 P)

NOTE - When a format contains AM or PM, the hour is based on the 12-hour clock. "AM" or "A" indicates times from midnight until noon; 12 AM is Midnight. "PM" or "P" indicates times from noon until midnight; 12 PM is Noon.

Sample Custom Date And TimeCodesThese are just a few examples to give you some ideas of what is possible with custom date and time formats. To display text next to a format code, enclose the text in quotes.
CODE DISPLAYS ****.  d mmm yyyy                     3 Apr 2000
.  mmm/dd/yyyy                   Apr/03/2000
.  mmmm dd, yyyy (ddd)     April 3, 2000 (Mon)
.  hh:mm "GMT"                  14:15 GMT
.  [h]:mm "elapsed hrs"       28:30 elapsed hrs
.  h A/P                                8 A
.  [blue]dd-mm-yyyy            03/04/2000  (displayed in blue)
. !   Format a date the wayyou want
https://support.microsoft.com/en-us/office/format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e

When you enter some text into a cell such as "2/2", Excel assumes that this is a date and formats it according to the default date setting in Control Panel. Excel might format it as "2-Feb". If you change your date setting in Control Panel, the default date format in Excel will change accordingly. If you don’t like the default date format, you can choose another date format in Excel, such as "February 2, 2012" or "2/2/12". You can also create your own custom format in Excel desktop.
.  *  Choose from a list of date formats
.  *  Create a custom date format
.  *  Tips for displaying dates
.
Format numbers as dates or timeshttps://support.microsoft.com/en-us/office/format-numbers-as-dates-or-times-418bd3fe-0577-47c8-8caa-b4d30c528309

When you type a date or time in a cell, it appears in a default date and time format. This default format is based on the regional date and time settings that are specified in Control Panel, and changes when you adjust those settings in Control Panel. You can display numbers in several other date and time formats, most of which are not affected by Control Panel settings.
.  *  Display numbers as dates or times
.  *  Create a custom date or time format
.  *  Tips for displaying dates or times
.

Answer 2

Hi ifavela01,

May I know if you still need help? May I know if you have tried the suggestion Rohn007 provided? If you still need help, you can post back and we will still try our best to help you.

At the same time, as Rohn mentioned, 44215 is number format, generally, we can go to Home > Number group > select Short Date. If you tried the suggestion above and same problem still persists, you can create a new Excel file and copy the content from problematic file to new file to check if the same problem will occur.

In the meantime, please also provide the product information and version of your Excel. You can go to Excel > File > Account and provide the full screenshot of it.

Best Regards,

Sukie