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?


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


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

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

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

| improve this answer | |
  • I have updated my post... – Stephan Aug 14 '13 at 11:47

Here is my solution thanks to indication from @mhoran_psprep


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.