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.

I need to find the time difference. I have a exacle sheet which I have made cells format as number before going to past the data in that exacel.

I have starttime in A column end time in B column like:

       A--------------- B   ---------- Time_diff_min(C)

20101104190507-------20101104190531

20101126145506-------20101126190644

20101120133943-------20101120185104

. . . like this but I need exact time difference in minutes.

I tried to find make difference like =(B1-A1) formula, but it does not given exact value because as it is in number format. but I need in mm:ss

share|improve this question

migrated from stackoverflow.com Jun 2 '11 at 8:54

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

    
What do those numbers represent? seconds? milliseconds? If it's a simple time-type value, then you'd have to convert to minutes:seconds, which would be simple math. –  Marc B Feb 17 '11 at 17:23

1 Answer 1

Formula for C1 (calculating from the values in A1 and B1

=(DATEVALUE(MID(B1,1,4)&"-"&MID(B1,5,2)&"-"&MID(B1,7,2))+
  TIMEVALUE(MID(B1,9,2)&":"&MID(B1,11,2)&":"&MID(B1,13,2))) - 
 (DATEVALUE(MID(A1,1,4)&"-"&MID(A1,5,2)&"-"&MID(A1,7,2))+
  TIMEVALUE(MID(A1,9,2)&":"&MID(A1,11,2)&":"&MID(A1,13,2)))

Then format C1 as a time and replicate for all rows

share|improve this answer
    
Thanks Mark, could you please let me know which format does I need to format(C1) because when I select column(C) and rigth click, select 'Format Cells' then I have selected Time then at the right pane which one do I need select for "MM:SS" of that time differance. –  mallikarjuna Feb 17 '11 at 18:09
    
The format mask that you need is a custom format of [mm]:ss - note the square brackets around [mm], which means that hours will be displayed as multiples of 60 minutes, so your third example value will show as 311:21 –  Mark Baker Feb 17 '11 at 20:18

Your Answer

 
discard

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