Excel keeps changing time or dates to incorrect format or completely wrong

You are experiencing "Excel adventures in date/time handling" ...

.

Excel has very aggressive automation built in to recognize data that even looks like a date or time which it then automgically converts it to excel date/time format. Often creating garbage data.  There are 2 components to how Excel handles dates and times.  First, excel converts the data to Excel "Date" data type.

Dates are a series of integers starting at Jan 1, 1900.  Today's date, Apr 8 2021, is serial number 44294. You can see the underlying serial number value of an Excel Date(time) datatype cell by changing it's datatype (Home tab > Number group > Number Format to "General". Changing a value to a date befor 1900 is exceedingly strange because Excel does not know how to handles dates as dates before Jan 1, 1900

Times are a decimal portion of a day. 

After Excel defines a cell as an Excel Date data type, it then applies formatting to display the date.  Although Excel starts with certain default date/time formats, you can apply "custom formatting" to change how the underlying date/time value is displayed on screen without actually affecting the underlying value. 

********************

In your case I think the simplest thing to do is FORCE excel to treat your times as text. 

When you are entering the values, prefix them with an apostrophe (the key in the upper left corner of an english keyboard), followed by the time value you want to see, ie

enter `3:00.

Excel will only display 3:00 as text

You will see the apostrophe in the formula bar and only if you go into edit mode in the cell

*******************

Here are some articles about how Excel handles dates and times

!   Date and Time Intro         2020 10 17    Mynda Treacy
https://www.myonlinetraininghub.com/excel-date-and-time
The objective of this post is to teach you how Excel handles date and time and provide you with all the tools you will need.
It’s designed to be read in conjunction with the accompanying Excel file, which you can download.
.  *  Windows Regional Settings
.  *  Excel Date and Time 101
.  *  Dates
.  *  Time
.  *  Date & Time Together
.  *  Entering Dates
.  *  Entering Times
.  *  Entering Dates & Time together
.  *  Adding/Subtracting Days from Dates
.  *  Subtracting Dates from one another
.  *  Adding Times to one another
.  *  Subtracting Time from Times
.  *  Subtracting Times from one another
.  *  Date and Time Shortcuts keys
.  *  'Good to Know' Stuff about Excel Date and Time
.  *  Date Modes: 1904 vs 1900
More links:
.  *  Every Excel Date and Time Function explained
.  *  Formatting Date and Time in Excel
.  *  Common Date and Time Calculations
.

! Convert date to serial number in Excel?
https://www.extendoffice.com/documents/excel/5293-excel-date-to-serial-number.html
If you receive a sheet with some dates, now you want to Convert these dates to serial numbers as below screenshot shown, how can you quickly solve it? Here I have some ways can do you a nice favor.
.  *  Convert date to serial number with Format Cells
.  *  Convert date to serial number with DATEVALUE
.

! The truth behind Excel dates, time and durationhttps://office-watch.com/2018/truth-behind-excel-dates/ ****Date handling in Excel can be confusing and it’s not your fault.  Microsoft could do a better job. Here’s a quick guide to how Excel handles dates and date arithmetic with the settings you should know.
.  *  What you see isn’t what’s saved-----------         .  *  Excel date and times
.  *  Date and Time arithmetic--------------------         .  *  Displaying dates and times
.  *  How Windows changes Excel dates------         .  *  Time Duration in Excel
ET MR Dates and Times.docx.

! Use Date & Time values in Excel – a handy guide   2018 06 20https://chandoo.org/wp/date-time-tips-ms-excel/
Excel date time features are very handy and knowing how to use Excel date values can help you save a ton of time in your day to day spreadsheet chores. Let us prepare for your date with the sheet using these 10 handy tips. So you see, Date and Time are in fact numbers in Excel. Just enter a date in your excel sheet and format it as number to see its equivalent numeric value. If a date is 20-June-2018 and excel represents it as 43271. Similarly, 9PM on 20-June-2018 is represented as 43271.875
.  *  Test whether a date is future or past
.  *  Find the number of days between two dates
.  *  Formatting dates
.  *  Auto-filling only weekdays
.  *  Find out the day of week from a given date
.  *  Highlight weekends using conditional formatting
.  *  Adding / Subtracting dates
.  *  Ensuring a valid date or time is entered in a cell
.  *  Insert today’s date, current time using key board shortcuts
.  *  Top Date functions for you
.  *  Common problems when working with dates in Excel
.

!   Format a date the way you 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
.
Custom Date / 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'd avoid the mmmmm format. How can you (or anyone else reading your worksheet) distinguish between January, June, and July, or between March and May, or April and August when looking at dates individually?
.
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), Optional hours
.  [mm]:ss             elapsed time in seconds   (such as 65:20), optional Minutes
.  [ss]                    elapsed time in seconds (such as 90)
.  ss.000                Decimal seconds to thousands
.  [h]:mm:ss.000    Decimal Seconds to thousands, optional hours
.  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 time format does not contain AM/PM or A/P the display is on the military 24hr clock.
.   If the 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 Time CodesThese 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 text)
.

Thank you, Ron, for trying to help. I'll try to explain what I'm doing: I need to build a spreadsheet that will be used to insert data into a program used to generate tags for our food pantry clients. I've been doing this successfully for over a year and have never encountered this issue before. When I enter time into the sheet, it enters correctly, but later switches to either a weird number [EX: 0000.0050...5] or switches to a date such as Mar. 5, 1899. The link between what I enter and what the system changes seems...well, unpredictable and inconsistent. If I have a column of 100 entries, perhaps 20 will be okay and the other 80 will be odd. I have tried everything I know to solve the issue, but nothing has worked. 

These are the steps I've tried:

1. Changed the entire column to be "text"
2. Changed the column to "time"
3. Added a space before entering the time information - something suggested on another Excel advice site
4. Created a new spreadsheet and reentered data rather than copying and pasting the data from the original sheet

I'm lost and frustrated.

Thanks -

Thank you, Jon and Gustavo -

I'll try to explain what I am building. I need to generate tickets that attach to boxes of groceries we deliver as part of our food pantry/food program. I have been doing this for over a year with no issue until now. I use a spreadsheet to track orders and then use the results to generate tickets using Publisher. The times are essential to guide our delivery drivers and pickup people as to when the order is to be delivered or picked up at our pantry. The time column is formatted as general. Until now when I export the sheet into Publisher the times have always just exported as entered. But suddenly they are exporting as odd numbers, odd dates of just blank despite being properly entered into the spreadsheet. I tried switching the format to "time" and also to "text" with the same result. To be clear, when the data is formatted as "general" it switched to a year date when imported into Publisher or into Word. I cannot show that here due to trouble trying to copy what is in Publisher without altering he formatting so it becomes unreadable. But here is the example of what happens in Excel when I format the columns as either "time" or "text". 

Just so you know, I finally figured a work-around, but it is clumsy and time consuming: I manually entered the time putting a letter before the number and adding an extra space. This gave me a workable result, but I still have no idea what this is happening. As I said, I have been doing the same thing, in the same way, since the whole Covid mess began last year and never encountered this problem until now.

Perhaps your Styles have become corrupted.

Suggestion:

1. Save and close a copy of the file as .xlsx or .xlsm
2. Ensure your system is showing the file extensions.
3. Append .zip to the existing file extension
   1. Open the zip file,
   2. Navigate to the xl folder, and copy styles.xml to a convenient location.
   3. Open styles.xml with a text editor.
   4. Locate the open and close cellstyles tags, and delete them and everything between.
   5. Save the file, and copy back to the zip file
4. Restore the original extension.

When the values change randomly, does the cell formatting also change?

For example: you formatted the cell as "Time" and typed 5:00. When the problem occurs, is the same cell still formatted as Time? Does the formatting also change?