Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have this spreadsheet:

   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?

share|improve this question
    
What does column C exactly do? –  Jacob Jan Tuinstra Aug 13 '13 at 18:07

4 Answers 4

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
I have updated my post... –  Stephan Aug 14 '13 at 11:47
up vote 1 down vote accepted

Here is my solution thanks to indication from @mhoran_psprep

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

share|improve this answer

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.