Forum Discussion
time format
ritainorbost I suspect you were entering e.g. 0810 as text. Now that you apply a custom format "hhmm", Excel expects you to enter time like 08:10 and will then format it as 0810. When you now enter 0810, Excel sees is as day number 810, starting from 1 January 1900, 00:00:00. You will thus see a date and time entry for "20 March 1902 00:00:00". Format the time section as hhmm will give you 0000.
But that doesn't solve the problem entirely. I highlighted the whole column and formatted time as hhmm. The first few lines entered exactly what I typed, but then the next row reverted to 0000, until I entered it as hh:mm. So why does it work for the first few rows, then not for the rest?
ritainorbost I can only replicate your problem when I first enter times in a Text-formatted column. If I then reformat the entire column as "Time", all old entries remain text, but any new entry is recognised as a number that will be formatted as Time. Entering 0810 is equal to the number 810 and this becomes 0000 as explained earlier. You can do some checks to verify the real nature of your time entries. In the cells next to them, type the following formula:
=ISNUMBER(<cell reference>)I suspect that this results in FALSE for entries that display correctly (because they are not numbers) and TRUE for the ones that do not (because they are numbers).
If this does not resolve the issue, you can perhaps upload a the part of your workbook that demonstrates the error.
