Excel time formatting

Question

Hi all, I'm working off a Mac using excel and I'm having problems with the time format. I've checked language (location) to make sure I'm using English (UK). Firstly, I only have 5 options to choose from:

*13:30:55

13:30:55

13:30:55

01:30:55 PM

1:30:55 PM

Secondly, even when I pick one of the options, regardless of the time I populate the cell with, it changes when I hit enter. For example, if I type in 14.55, it will change to 13.12. What am I doing something wrong? Incredibly frustrating.

Any help would be great.

Answer 1

If you enter . it will be interpreted as a decimal point. 14.55 is treated as 14 days and 55/100 of a day. 55/100 corresponds to 13:12. You must use : as separator when you enter time (unless you use VBA to convert 14.55 to 14:55).

You can create your own custom time format:

• Select a range of cells.
• Click More Formats... in the number format drop-down on the Home tab of the ribbon.
• Select Custom in the Category list.
• Enter your preferred format in the Type box.
• Click OK.

See Format numbers as dates or times for the format codes you can use.