Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time Format (2003 SP2)

    Good Morning

    I have tried to format some cells to HH:MM using the Custom option expecting that when I type 0600 (tab) I would be shown 06:00 however I get 00:00 if I type in 06 then : (tab) I get 06:00

    This is my first attempt at doing anything with hours so is this normal behaviour or is there a (simple) way to tell a cell that if I type in 0630 it would show 06:30, 0345 - 03:45 etc.

    Also I will eventually be trying to do time sheet calculations using these figures so would it confuse matters by trying to change it this way.

    Thanks

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Time Format (2003 SP2)

    If you enter 0630 without the colon, excel has no way to know that you want it to be converted to a time rather than a number. The colon is the indicator to excel to "translate" the number into a time.

    Checkout the code from Chip Pearson at Date And Time Entry to do this

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Format (2003 SP2)

    Thanks Steve

    VBA is a road that I fear is not for the likes of my mere mortal status, because it showed in the Custom example as 00:00 that is how I expected it to appear and though perhaps I had done something wrong.

    Cheers for the quick response

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Time Format (2003 SP2)

    The format cells is a "display".

    Excels stores time and date in units of 1 day. It is the number of days since 12/31/1900. Integer values have "times" of "00:00". Times are the fractional part. the number 0.5 (eg) is 12 Noon. the time 6:30 is equivalent to 6.5/24 = 0.270833. Excel will convert a time entered as "06:30" to the "time value" of "06:30 AM" which has a numeric value of 0.270833 and will format the cell as time. if the format of the number entered as "06:30" is changed to "General" you will see the value XL is using.

    When 0630 is entered into a cell, it is not consisdered a time and not converted so it has the value of 630. If formatted as a "date" it will be September 21, 1901 but since it has no fraction (or the fractional part is 0 depending on how you want to view it) it is essentially "Midnight" the start of the day and will display as time "00:00".

    Is this clearer?

    Chip Pearson's code converts the "0630" since XL is not doing it directly since it expects the colon.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time Format (2003 SP2)

    Thanks Steve

    That made it clearer for me, perhaps I will give the VBA a bash

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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