Custom time format not working - the digits entered are cganging to zeros

Hi All,

While since I've been here. Sorry, but I am probably being very stupid as below.

I am trying to format cells so that when I enter the digits for a time, it displays in hh:mm format without having to enter the colon. I define the cell as Custom and then select the hh:mm format from the list. I then enter the time as, eg, 1400 and want that to display as 14:00, but it changes the entered digits to all zeros although the format selected displays correctly. Can't suss it. Bats in the belfry.

HELP please.

Many thanks.

Try a custom format of - 00":"00

Note that the number will be 1400 that is contained in the cell, and not 14:00 (hh:mm) if you are going to use it for calculations.

Thank you, Kersplash. Works a treat. Still don't get why what I had tried didn't work, but there are always more questions than answers.

Again, many thanks.

Start here to find out about how Excel stores Dates and Times;

https://www.exceltactics.com/definit...s-times-excel/

Your entry of 1400 was trying to display day number 1400 from Jan 1 1900 with a time of 00:00, and your format of hh:mm was making it display just the time component of the date and time, therefore 00:00

Thank you to both Kersplash and MrShorty for your prompt and clear replies. I will take your advice and follow the links you have kindly supplied.

What it is to have friends..........