Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How can i find the time difference from like "9-10:30" formatted cell.What is the formula to find the "1.5" hour or "90" minutes from that cell.

share|improve this question

migrated from stackoverflow.com Apr 9 '13 at 15:41

This question came from our site for professional and enthusiast programmers.

2 Answers 2

I'd advise you, if possible, to put the two time values in separate cells, e.g. 09:00 in A2 and 10:30 in B2 then use this formula in C2

=(B2-A2)*24

format C2 as number to see 1.5

to get 90 replace 24 in the formula with 1440

If you can't split the data but can make it consistent like 09:00-10:30 or 15:30-19:15 (with times always shown as 5 characters including ":") then you can use this formula

=(RIGHT(B2,5)-LEFT(B2,5))*24

share|improve this answer
    
I did '=HOUR(MID(B2,FIND("-",B2)+1,1000))-HOUR(LEFT(B2,FIND("-",B2)-1))' but not works. –  Ufuk özkanlı Apr 9 '13 at 12:16
    
You'll need to use more than HOUR if you want to take account of minutes.....Did you try my suggestion? If you can't split the data, can you at least make it consistent like both parts being time values e.g. 09:00-10:30? –  barry houdini Apr 9 '13 at 12:20
    
now I split to cell into "9" and "10:30" then =(B2-A2)*24 gives me -205.50 –  Ufuk özkanlı Apr 9 '13 at 12:25
    
That's because 9 is a number and 10:30 is a time value - I suggest you use 09:00 and 10:30 to be consistent, otherwise you need to use =B2*24-A2.....but that will only work if B2 is a time and A2 is a number..... –  barry houdini Apr 9 '13 at 12:36
    
What is the easiest way to change "9" column to "9:00" –  Ufuk özkanlı Apr 9 '13 at 12:41

Assuming this value is in A1:

 =TEXT((RIGHT(A1, LEN(A1) - FIND("-",A1))&IF(COUNTIF(A1,"*-*:*")>0,"",":00")) 
     - (LEFT(A1, FIND("-",A1) - 1)&IF(COUNTIF(A1,"*:*-*")>0,"",":00"))
     ,"hh:mm")
  1. Get right most time: right(A1, len(A1) - instr(A1, "-"))

  2. Add :00 if required: IF(COUNTIF(A1,"*-*:*")>0,"",":00")

  3. Get left most time: left(A1, instr(A1, "-") - 1)

  4. Add :00 if required: IF(COUNTIF(A1,"*:*-*")>0,"",":00")

  5. Format the hour based number into hours and minutes: text(..., "hh:mm")

share|improve this answer
    
I edited your formular like that '=TEXT(RIGHT(E3, LEN(E3) - FIND("-",E3)) - LEFT(E3, FIND("-",E3) - 1),"h:mm")' but not working –  Ufuk özkanlı Apr 9 '13 at 12:38
    
If you followed the formula what is the issue? –  glh Apr 9 '13 at 12:44
    
no problem in formula but result is nothing –  Ufuk özkanlı Apr 9 '13 at 12:47
    
I forgot to add code to add the :00 if none exists, see above –  glh Apr 9 '13 at 13:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.