Why does this date/time format break when entering data?

Question

I'm entering a timestamp (date+time) into cells, but only want it to display the time, not the date. So, for example, the Excel cell is formatted as "hh:mm" but the data is entered as "8/7/201 16:44". (I'm actually using control-; space control-shift-; to enter it.)

If a date+time is already entered, Excel has no problem with only showing the time of day. But if I enter new data into a cell, it insists on using a format that shows the full "8/7/201 16:44", instead of just the time of day.

I am only displaying time of day because, though I want to log the date too, I only need to see the time as of this day's work day, for now. (When I analyze the data days later, I'll want to see the date, too.)

I already have way too many columns to show the full width. this quirk means I can't see the time without making the column much wider, which I do not want to do. It's like, come on Excel... what's wrong with you? You know how to handle dates and times!

Laugh. So ... Why won't Excel recognize the format I'm asking for, even though it will take that format if I apply it after I've entered data?

Thanks if you can help!

Answer 1

Now it's getting weird. I'm using Excel 2019, by the way.

If I apply the custom format hh:mm, then enter a date and time, Excel changes the number format as you describe - to yyyy-mm-dd hh:mm in my case.

But if I apply the custom format hh:mm;@ and then enter a date and time, Excel leaves the number format as it is.

Why? No idea...

Answer 2

But if I apply the custom format hh:mm;@ and then enter a date and time, Excel leaves the number format as it is.

Why? No idea...

It's a feature. :-)

The first step to understand what happens behind the scene is to know that you can not enter a date (or number) into Excel, NEVER! What you write into Excel is a text, ALWAYS! Sounds strange at the first glance, but if we go into the details: The keyboard sends ASCII codes to the system, which are passed to Excel.

Based on the regional settings (not based on the cell format!) Excel interpret the text. And if it looks like a date, excel performs calculations in the background to convert the text into a number. This number is put into the cell.

Now we came to the cell format.

If we make a new file any cell is formatted as General. And if we write a "text looks like a date" into the cell, Excel performs the calculations and changes the number format of the cell to something that shows the number in a way that looks like what you've entered.

That's the reason why you see a date when you entered e.g. "1/2" into a cell or a time if enter e,g, "1:2" or a date and time if you enter "1/2 1:2".

The cells formats "h:mm" or "d/m/yy" or "d/m/yy h:mm" are not explicit and so allows Excel to change the number format.

Any format that has a specialization "fixes" the cell format. Here's an example to reproduce:

BTW, did you noticed that the cell shows AM/PM even if I removed the AM/PM part from the number format?
The regional settings of the system comes in affect here!

Andreas.

Answer 3

Gosh guys ...

If I can apply the formatting after a timestamp is entered, and the format works properly after the fact, doesn't that mean it's a proper date/time?

After all, I am using Excel's own hotkeys to enter it. If you hit control-; + space + control-shift-; this is what it looks like, copied directly out of the Formula Bar: "8/7/2021 17:55:00".

Of course, I have applied the format to the cells before entering the data. Otherwise my post doesn't make sense.

I've tried applying it to the entire column. Also tried Format Painter from cells that have it properly (they have date+time data but only show hh:mm) to cells without data.

Both ways, Excel still breaks when I enter a new date+time and insists on displaying the entire thing. It shows as e.g. "8/7/201 17:48" in the cell (and yes, the Formula Bar shows seconds). But because I have my columns very narrow, what is actually shows is, of course, "#####", because you can't display an entire date+time in a narrow column that I only want to show hh:mm. If I widen the column, then I can see what it actually did. As described here.

If I look at a cell whose format broke this way, it has a format of "m/d/yyyy hh:mm", which is in my Custom Formats list. (But of course, I didn't choose it ... it has overriden the format I applied and does this itself. As I've been saying. This is my whole problem.)

All I can think of is that Excel 2013 is broken somehow on this fine point... somehow it can't deal with the fact that the date isn't in the cell's format, so it overrides it.

Have either of you tried what I'm talking about? Although you probably have Excel 365.

Answer 4

Can you apply the custom format hh:mm afterwords successfully?

I assume you have applied the custom format before you enter the date and time?

What does the date/time look like on the Formula Bar?

If the entry is being treated as Text it will show as: 8/7/2021 2:08 PM

If it is being treated as a date and time is will show: 8/7/2021 2:17**:00** PM

In other words your  "8/7/201 16:44" suggests that the data is being treated as Text. You should apply the date/time format before you make the entry.

Answer 5

What happens if you format a column as hh:mm before entering any data?