How do I make a custom Time format stick?

Question

Level 1

8 points

How do I make a custom Time format stick?

I'm a relatively infrequent user of Numbers, so I may have missed something!

What I want to do is enter Time into a cell simply as 4-digits, ie 7:08am would be entered as 0708 (sometimes referred to as Military Time), and for that entry to appear in the cell as 0708.

I've created a custom format for Cells to format Time as 4-digits, but each time I enter a time the cell reverts to the 'Automatic' data format!

This is a screen shot of the Custom Date and Time Format menu:

The problem is that when I type 0708 into a cell it returns 708, not 0708, and also returns the cell to the Automatic format, rather than the custom Military Time format that I created

This is the Smart Cell View:

Can someone/anyone please tell me what I'm missing and how to end up with 0708 displayed in the cell?

Many thanks in advance,

jack

iMac 27″, macOS 11.2

Posted on Feb 4, 2021 6:36 PM

Reply

Answer 1

Best reply

Barry

Level 9

59,867 points

Feb 5, 2021 1:32 AM in response to Majestical

I suspect, going from the reversion to Automatic, that Numbers does not recognize the input value "0708" as the time part of a date and time value.

Have you specified, as part of your custom format, "none" for the date part?

Try entering that time as 07:08.

Are you using these values in calculations? If not, you could set the cell(s) data format to Text, and enter the times as you want to see them.

Regards,

Barry

Reply

Answer 2

Barry

Level 9

59,867 points

Feb 5, 2021 6:11 PM in response to Majestical

I'm unsure as to why Numbers couldn't/wouldn't/didn't convert the 4-digit string to hh:mm, so I'll research that a bit to see if I can find a way to do that.

Numbers didn't convert the four digit string to the hh:mm format as it did not recognize 0708 as a 'time'. numbers wll see that string as a number if the cell's data format is set to automatic or as a text string if the cell's data format is set to Text.

You could enter the 'time' as shown into a cell formatted as text, then use a formula in a second cell to convert that text to a 'time' part of a date and time value to be referenced in formulas.

The 'formula cell does not need to be visible to the user and can be placed on the same table or a different table.

Regards,

Barry

Reply

Answer 3

Majestical Author

Level 1

8 points

Feb 5, 2021 3:35 PM in response to Barry

Thanks Barry, your answer was spot on.

Once I used the colon in the entry all was good, so the custom format seems to have become redundant. (And yes I had selected "none" for the date part, and yes I need to use these values in calculations)

I'm unsure as to why Numbers couldn't/wouldn't/didn't convert the 4-digit string to hh:mm, so I'll research that a bit to see if I can find a way to do that.

Any suggestions welcome!

Reply

How do I make a custom Time format stick?

Similar questions