# Thread: Time Format (2003 SP2)

1. ## 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

2. ## 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. ## 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

4. ## 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. ## Re: Time Format (2003 SP2)

Thanks Steve

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

Cheers

Steve

#### Posting Permissions

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