# Excel 2003: convert a numeric cell into time for calculations

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!

-

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
``````
-
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.

-
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