Question

Locked

Can Excel be set to automatically add a colon in a cell formatted for time?

By dgh ·
Let's say you enter "1300" and what you would like to have displayed is "13:00". It would be nice to just enter 1300 and press enter instead of entering 13, pressing the shift key and the colon key and then enter 00 and pressing enter.

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

right click

by PurpleSkys In reply to Can Excel be set to autom ...

format cell choose time?

Collapse -

Doesn't work

by neilb@uk In reply to right click

Cell contents go to to 23/07/1903 00:00:00 with 00:00:00 displayed.

Collapse -

you have to use the serial date equivalents . . .

by Who Am I Really In reply to Doesn't work

after formatting the cells to Time use the serial number equivalents

to have date / times enter the correct time

the format is #####.#####
where
#####. is the date portion and
.##### is the time portion
ie.

18/02/1982 12:00:00 = 30000.5
18/02/1982 13:12:00 = 30000.55
18/02/1982 13:19:12 = 30000.555
18/02/1982 23:59:59 = 30000.99999

to skip the date part just use the numbers after the .

ie.
to get 12:00:00 with no specific date assigned use .5

00:00:00 (midnight) = 0
all other time numbers are lower than 1
ie.
23:59:59 = .99999

Collapse -

I don't think that's what the OP wants

by neilb@uk In reply to you have to use the seria ...

He wants to be able to type, say, 1645, into a cell and have it display 16:45.

Collapse -

that's not directly supported . . .

by Who Am I Really In reply to I don't think that's what ...

to avoid typing the :

you have to type the serial equivalents

1645 = 02/07/1904 00:00:00
.1645 = 03:56:53

Collapse -

What I don't get

by santeewelding In reply to Can Excel be set to autom ...

Is, why the colon? Is this some method of displaying 24hr (aka, military) time I haven't heard of?

Collapse -

digital clocks always use colon :

by Who Am I Really In reply to What I don't get

10:21

12:15

etc.

in 12 hour clock you append AM / PM

in 24 hour you start at 0 add leading the zero and increment past 12 to 23
00:00
01:00
02:00
03:00
04:00
05:00
06:00
07:00
08:00
09:00
10:00
11:00
12:00
13:00
14:00
15:00
16:00
17:00
18:00
19:00
20:00
21:00
22:00
23:00

good luck getting excel to display an analog dial clock in a cell

Back to Software Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums