How to create a custom time format

Excel provides a vast number of common time formats. However if you do need a format that is not included in the built-in list then you can create your own custom format as follows:

  1. Right-click on the cell where the date is.
  2. Select Format Cells.
  3. When the dialogue box opens select the Number tab.
  4. In the Category list to the left select Time.
  5. In the Type list to the right select the time format closest to what you want. A sample format will show above.
  6. In the Category list to the left select Custom. A list of custom time formats will show to the right, and the format you selected will show in the Edit Box above.
  7. Modify the format in the Edit Box and watch the sample.
  8. When you are satisfied click OK.
  9. The new format will be applied to the cell and will also be added to the list of custom formats.

Applies to: Excel 2003

Comments

  1. Nana says:

    Could you help?

    I am trying to produce a spreadsheet for a 35hr week, which includes weekend and night working. We work flexible hours. core hours are 8am-10am (for start times) and 5.30pm-7pm for finish times.
    Night shifts start from 7pm to 2am.

    I have done the various columns for the start and finish times. However, when I come to add the column for the week,containing the total hours worked, and authorised absence, i am not getting the result I should get.

    regards

    Nana

  2. admin says:

    I am sorry but it is difficult to understand your problem without some examples. Anyhow here are some links on how to sum times. I hope they will help:

    http://www.exceldigest.com/myblog/2009/02/08/how-to-add-or-sum-times/
    http://www.exceldigest.com/myblog/2009/11/29/how-to-some-times-greater-than-24-hours-in-excel-2007/

    If the problem is not resolved I suggest that you try Excel Help Forum. There you can post a sample of your data to help others understand the problem. Here is the link for the forum:

    http://www.excelforum.com/

  3. Pau says:

    Good day. I cannot find a format that means zero hours and thirty minutes (0:30). What I get in the formula bar is 12:00 AM instead. How can I make it display 0:30? So I can add or subtract the other amounts of time in my sheet.

    Please help.

    Thanks.

    • admin says:

      you have to enter your time as 00:30 to be displayed properly. Otherwise if you enter it as a pure number then you have to use a formula in another cell to convert it to time like ” =TEXT(A1;”00\:00\:00″).

  4. Wilson says:

    Is there a way to make the time only show hours if the hours are non-zero?

    That is, if the time is 0 to 59:59, it shows MM:SS, but if it’s greater, it shows [H]H:MM:SS.

    I tried [h]:MM:SS, but that didn’t work.

    I only have Excel 2000 available to me, so if this has been fixed/implemented in a later version, I guess I’m out of luck.

  5. Wilson says:

    Thanks a lot. I asked a reasonable question politely and was snubbed. Lovely. You could at least have answered, “Sorry, I can’t help you.”

    • admin says:

      Please note that this is a personal website which is maintained by only one person. Although I do my best to help people, it is often very difficult to find a free time to reply to questions in a timely manner.
      Now let us go back to your question. I don’t think you can do conditional formatting in Excel 2000 the way you want. However you can try the following VBA code as a user defined function. Put your original time in a column and the function in another column (you can hide the first column).
      Function cformat(rngTime As Range)
      Dim varTime As Variant
      If Hour(rngTime.Value) = 0 Then
      varTime = Minute(rngTime.Value) & ":" & Second(rngTime.Value)
      Else
      varTime = Hour(rngTime.Value) & ":" & Minute(rngTime.Value) & ":" & Second(rngTime.Value)
      End If
      cformat = varTime
      End Function

      See how to create a user-defined-function here.

    • REALLY? says:

      You are upset because you asked a free question on a free website and didn’t get the answer quick enough for you?

      Wow – what a doosh.

      Try searching other forums for your answer if you need immediate gratification.