Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am using Excel at work and I have a list of users' online times.

I get a total say 180:30:00, which is 180hrs 30 mins and 0 seconds.

I need to keep this cell as the time, but I want to work out how many sales per hour they have made, so I need another cell linking to the total cell. But I need the format to be in 180.5.

The format I use for the 180:30:00 cell and all my times is [H]:mm:ss. To get the time into decimal I was going to use the left middle and right formulas to break the time into 3 different cells and then do the hour + minute/60 formula, but I have came across an issue.

When I click on the cell 180:30:00 on the top line where you can enter formulae I have 07/01/2001 180:30:00. So when I apply the left middle and right formula it picks up the date as well.

Can you please explain to me how I could change the format of the cell to lose the 07/01/2001 – or is it always there when you involve time at all?

I'm not at work so I can't post a screenshot right now.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

I would assume that the date/time values in your spreadsheet have been entered as strings; otherwise, Excel would have changed 07/01/2001 180:30:00 to 07/08/2001 12:30:00 in the Formula bar. Excel carries over hours in excess of 24, converts them to days and adds them to the date.

Use the formula that you have to extract the time segment of the date/time string. I believe you have something similar to this:

=RIGHT(A1,FIND(" ",A1,1)-2)

Multiply the result by 24, then set the cell format to General to get 180.5.


Generally, though, you can extract the time from date/time values using this formula:

=A1-INT(A1)

If A1 contains 1/1/1900 12:30:00, this formula returns 12:30:00. Make sure the cell uses the number format [h]:mm:ss.

To get hour fractions instead, change the formula to:

=24*(A1-INT(A1))

From which you'll get 12.5.

share|improve this answer
    
Thank you kaze makes sense and i will give it a go monday morning –  user68062 Aug 19 '11 at 21:49
add comment

Excel stores dates and times as a number representing the number of days since epoch (1900-Jan-0), plus a fractional portion of a 24 hour day. The date portion will always be there.

see this page for more information on date and time calculation in excel

share|improve this answer
    
Thank you kaze makes sense and i will give it a go monday morning –  user68062 Aug 19 '11 at 21:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.