1

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!

2 Answers 2

3

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
4
  • 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, 2011 at 14:38
  • You are my hero.
    – Pitto
    Feb 2, 2011 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, 2011 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, 2011 at 17:07
0

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.

5
  • 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, 2011 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, 2011 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, 2011 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, 2011 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, 2011 at 14:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .