# How to convert datetime interval to hours?

``````   A            B        C       D          E
1  08/13/2013   02:10    4
2  08/13/2013   02:19    10      00:09:00   160
``````

In D2, I have this formula : `=if(B2="";"";to_date(concatenate(A2;" ";B2))-to_date(concatenate(A1;" ";B1)))`

In E2 I have this formula : `=if(D2="";"";(C2-C1)/D2)`

But E2 outputs the wrong result, 160. I want it to be 40 (=(10-4)/0.15). `0.15` is the value in D2 converted to hours.

How can I do that?

-
What does column C exactly do? – Jacob Jan Tuinstra Aug 13 '13 at 18:07

I would suggest using the following formula to calculate the date/time difference in `D2`:

``````=(A2-A1)+(B2-B1)
``````

This eliminates floating point errors as you can see in the example file I created: DATEDIFF

-

Google as well as Excel treat all times as decimal days.

Therefore 9 minutes is not 0.15 instead it is 9 minutes divided by the number of minutes in a day. or 9/1440 or 0.00625.

-

You say you

"want it to be 40 (=(10-4)/0.15)"

Seems to me that you need to add (c2-c1) into your formula, as well as worrying about the time difference.

-
I have updated my post... – Stephan Aug 14 '13 at 11:47

Here is my solution thanks to indication from @mhoran_psprep

`=if(D2="";"";(C2-C1)/(D2*24))`

-