Question Locked

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

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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.

8 replies

by who am i really

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

by santeewelding

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

by who am i really

10[b]:[/b]21

12[b]:[/b]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[b]:[/b]00
01[b]:[/b]00
02[b]:[/b]00
03[b]:[/b]00
04[b]:[/b]00
05[b]:[/b]00
06[b]:[/b]00
07[b]:[/b]00
08[b]:[/b]00
09[b]:[/b]00
10[b]:[/b]00
11[b]:[/b]00
12[b]:[/b]00
13[b]:[/b]00
14[b]:[/b]00
15[b]:[/b]00
16[b]:[/b]00
17[b]:[/b]00
18[b]:[/b]00
19[b]:[/b]00
20[b]:[/b]00
21[b]:[/b]00
22[b]:[/b]00
23[b]:[/b]00

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

by Member 25348569

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

by who am i really

to avoid typing the [b]:[/b]

you have to type the serial equivalents

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

This discussion is closed to new replies.