Results 1 to 6 of 6

Thread: Time Format

  1. #1
    Star Lounger
    Join Date
    Mar 2009
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,

    I want to put a custom format in a cell for time. I want to be able to enter as example 8:36 or 13:23 where all I have to do is enter the numbers and the colon will be inserted automatically. I used to have a format for this and can't find or remember it.

    Thank you,

    Mike

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    You could use a custom number format 0\:00 or 00\:00
    If you enter 823, you'd see 8:23 or 08:23 depending on which of the two formats you chose.

    BUT...

    This is just a way to make the values LOOK like times. Excel still stores the numbers the way you entered them, you can't directly perform calculations with them, such as adding or subtracting times. You'd need to convert the values to real time values in your formulas.

    Also see the topic Entering Times Quickly.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='782666' date='01-Jul-2009 17:15']You could use a custom number format 0\:00 or 00\:00[/quote]
    Hello Hans

    What is the significance of the backslash in your custom format? I find that (000) 000-0000 formats a number nicely for a North American telephone number without the backslash; yet it is required to format for time.

    T.I.A.
    Regards
    Don

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    I think the \ is needed to show that the : is a literal colon character, and not a separator between different number formats.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Stuart nailed it. When the user enters a value such as 820, it is not a "real" time. If you tried to format it as a time, you'd see 12:00 AM (or 0:00 if you use a 24 hour format). The custom format 0\:00 simply inserts a colon as a literal character to make the value look like a time. The backslash \ can be used to insert characters in number formats that otherwise would have a special meaning.

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thank you Stuart and Hans.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •