# Thread: Formatting cells as hours, not time in excel (2000)

1. ## Formatting cells as hours, not time in excel (2000)

Hello,

I'm sure I've done this before, but it' s been a while and I've forgotten <img src=/S/confused.gif border=0 alt=confused width=15 height=20> .

I'd like to enter time spent on something into a cell as hours:minutes:seconds, but when I enter this into Excel, it interprets it as a time of day. How can I get Excel to stop thinking that 5:30:24 is 5:30 am and understand that it is actually 5 hours, 30 minutes and 24 seconds of total time?

2. ## Re: Formatting cells as hours, not time in excel (2000)

They are equivalent numbers to excel. you can format the cells as (no quotes):
"[h]:mm:ss"
to display (and it will go over 24 hours)

If you want the DECIMAL hours (5.50667) format as a number and multiply it by 24 (Excel stores all dates and times as "fractions of a day") so you multiply the number of days by 24 to get hours. For minutes mult by 24*60 for seconds 24*60*60, etc

Steve

3. ## Re: Formatting cells as hours, not time in excel (2000)

<img src=/S/hello.gif border=0 alt=hello width=25 height=29> Bob

How are you entering these numbers, are you using the ":" to separate the digits? What happens if something takes 26 hours, 30 minutes, and 5 seconds, how will you enter this?

Try one of these:

Number Format under Time = "h:mm:ss AM/PM"

or

Number Format under Time = "h:mm:ss" for what we call Military time, which is based on 24-hours per day, and not AM and PM, so 1:00 PM will be 1300hr. and 1:00 AM will be 0100hr.

Hope this helps, and I hope I got your question right?

Wassim

4. ## Re: Formatting cells as hours, not time in excel (2000)

Hello,

Thanks for the replies, but I'm still stuck.

The question is based on imported rows that contain tasks that take certain amounts of time to perform. The elapsed time of these tasks is displayed as hours, (sometimes more than 24) and minutes and seconds. So when the numbers come in, if a task takes 34 hours, 6 minutes and 37 seconds to complete, we would like the column to read 34:06:37. Now, the second problem is (and should be solved by the solution to the first) that we would like to compare times to get a difference between actual elapsed time and projected elapsed time. So if the actual elapsed time was 34:06:37 and the projected time was 38:00:00, we would like to come up with a difference of 3:53:23.

5. ## Re: Formatting cells as hours, not time in excel (2000)

OK Bob

One thing, earlier you said you are entering these numbers, now its importing?!

OK what process are you using to import the data, is the data delimited? If so, maybe that is the key. If they are, store them into separate columns, and display the data you want to see in a column by itself.

So now when you want to do the calculations, you do it on the imported columns, and you display the result into a column.

It would be a great idea if you could post a sample of the data source, and then also the worksheet and maybe we'll try to make it work for you.

Now this is what we call "Shooting from the Hip", maybe there is a better solution based on the data source.

HTH

Wassim

6. ## Re: Formatting cells as hours, not time in excel (

Try looking at this thread. It deals with a number of the issues that you are raising. HTH

7. ## Re: Formatting cells as hours, not time in excel (2000)

Hi Bob

I think the attached 'appears' to do what you want? All I have done is taken the advice of others and formatted ALL the cells in question to [h]:mm:ss and the math appears to work? Does this help any?

Regards
Peter

#### Posting Permissions

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