time format

[ritainorbost]

Hi,

I'm having trouble getting the time format that I want.  I tried custom format for a column, which worked for the first 6 entries in the column, but now it's reverted.  I want 24 hour format but I don't want the colon between the hour and minutes.  I right-clicked on format cells, time, custom and put in hhmm.  At first it entered (eg) 0805, 2330 etc.  Now when I tried to enter 1710, it shows 0000.  Perhaps I'm lazy, but I'm not a typist and it annoys me when I have to enter more than necessary.

[#2]

@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.

[#3]

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?

[#4]

@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.