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:
- Right-click on the cell where the date is.
- Select Format Cells.
- When the dialogue box opens select the Number tab.
- In the Category list to the left select Time.
- In the Type list to the right select the time format closest to what you want. A sample format will show above.
- 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.
- Modify the format in the Edit Box and watch the sample.
- When you are satisfied click OK.
- The new format will be applied to the cell and will also be added to the list of custom formats.
Applies to: Excel 2003
Related posts:
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
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/
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.
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″).
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.
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.”
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.
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.