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 have some data in excel, all numeric, like:

9.00

10.35

19.10

I need to do calculations like:

I got at work at 9.00, I got out at 10:00 = I've worked 1 hour

How can I achieve this if all my data are in numeric cells? I need to do a convert but I can't find how...

Thanks!

share|improve this question

2 Answers 2

up vote 3 down vote accepted

This formula will convert a numeric value like 9.00, 10.35, etc to a time like 9:00 AM, 10:35 AM, etc.

=TIME(INT(A1),MOD(A1,1)*100,0)

That should get your data into the proper format. Then you can apply this formula to two different time values to find the difference in hours:

=(B1-A1)*24
share|improve this answer
1  
Dang, beat me to it. I was testing with that just now. The only problem is if you have it in a "10.35" format instead of "1035", it won't work properly. Try to do a find & replace for decimals. Can replace them with nothing and it works wonders. Overall what I would do for the asker's problem, is make three new columns, post the former formula in the first two (for converting decimal into time) and then the latter into the last (to make the difference). Then copy/paste values if you can't have those three other columns permanently. –  Duall Feb 2 '11 at 14:38
    
You are my hero. –  Pitto Feb 2 '11 at 14:49
    
@Duall I don't understand what you mean. The formula works perfectly with the decimal format, i.e. '10.35'. –  Sux2Lose Feb 2 '11 at 16:28
    
Woops. You're right, it does work. I didn't do yours exactly, I had a modifier on the first A1. I apologize, you were right. –  Duall Feb 2 '11 at 17:07

Okay, so let's start a new sheet for you to practice!

Column A shall be "Start", B shall be "End", and C will be "Hours". Let's go into the data type window. Right-click on A, and hit 'format cells'. Make sure A and B are times. (I use h:mm AM/PM), and make sure C is "number", give it two decimal points. You got to work at 9:00 AM. You left at 10:00 AM. Put them in their respective columns. Now, in C type the following:

=(B1-A1)*24

Should show "1.00" in C.

share|improve this answer
    
Great hint! The problem I have is that I already have values in "Start" and "End"... These values are numeric and written like I did in question: 9.00 10.35 19.10 How can I convert this data to time correctly? –  Pitto Feb 2 '11 at 14:08
    
Ah, in that case, the reverse is true. do "=(B1-A1)\24" and make sure that C is in a time format. For this I would suggest the h:mm;@ format code. Only problem with that is if the start is bigger than the end it doesn't work, but assuming they are based on time that shouldn't be a problem. That is unless you have a time machine you'd like to share~ –  Duall Feb 2 '11 at 14:11
    
I doubt 10.00 - 9.35 would yield 0.25? (I think you'd need something like TIMEVALUE for that.) –  Arjan Feb 2 '11 at 14:19
    
Ah, you are correct @Arjan. I was thinking in putting the decimal time back into time, not time in a number format into time. –  Duall Feb 2 '11 at 14:24
    
Mmmh... Not working... I have 9.40 in A and 11.35 in B. In C, correctly formatted, I put "=(B1-A1)\24" And the calculation is 1.57 –  Pitto Feb 2 '11 at 14:24

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.