How to format cells in excel to enter 24-hour time?

Question

When I enter a value as 13.25, the value should be changed as 13:25 and not 06:00. How to format the cell for that?

Answer 1

You can use the following solution

The following formula converts decimal minutes to the hh:mm:ss format in Microsoft Excel

   ="Minutes"/1440

Where "Minutes" is a decimal number between 0.0 and 1440.0. The portion of the number to the left of the decimal refers to the number of minutes. The decimal portion of the number refers to seconds. 1439.0 minutes is equal to 23 hours and 59 minutes and converts to "23:59:00". There are 1440.0 minutes in 24 hours. Using the formula shown above will return a serial number. To display the serial number in the appropriate format, choose Number from the Format menu (or, on the Format menu, click Cells, and then select the Number tab) and select hh:mm:ss.

To convert hours and minutes from a decimal number to the hh:mm:ss format use the following formula:

   ="Hours.Minutes"/24

Use this formula to convert 4.5 minutes to 0:04:30:

   =4.5/1440

Decimal hours.minutes:

Use this formula to convert 4.5 hours to 04:30:00:

   =4.5/24

from http://support.microsoft.com/kb/75618

Answer 2

Just enter 13:25. the dot separator always mean decimal, so even if you could convert 13.25 to a time, 25 would mean 1/4 of an hour, not 25 minute, so the time you'll get will be 13:15.

Answer 3

If you need to change 13.25 to the time value of 13:25 (not 13.15).

Then you can use TimeValue and Substitute

Assuming your time is in cell A1

=TIMEVALUE(SUBSTITUTE(A1,".",":"))

Then change your cell format to HH:mm or use the TEXT function

=TEXT(TIMEVALUE(SUBSTITUTE(A1,".",":")),"HH:mm")

Answer 4

Assume A3 stores 13.25 The way i resolved this problem was first multiply all the values by 100. Then in formatting options, use 00/:00 to get the value in cell as 13:25

If you want to perform any time based operation, it will be better if you convert it into time using the method below:

=Time(Trunc(A3/100),MOD(A3,100),0)

It will give the value in time which can be formatted suitably to 24-hour format of hh:mm or h:mm AM/PM.

Answer 5

When you format cells, use hh\:mm:ss for 24hr format instead of hh:mm:ss