up vote 0 down vote favorite
share [g+] share [fb]

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

link|improve this question
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
feedback

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

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

1 Answer

up vote 0 down vote

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

link|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
feedback

Your Answer

 
or
required, but never shown
discard