Excel formula query

Question

How to convert minutes seconds into decimal format in Excel. eg: I have the data 28 minutes, 116 seconds

which needs to be first calculated to be read as 29 minutes, 56 seconds. And then it needs to be converted into decimal format.

Answer 1

Does the data always show minutes and seconds? What about hours? For just minutes and seconds in A2 try this formula

=SUM(MID("000"&A2,FIND({"min","sec"},A2)-1,3)/{1440,86400})

format result cell as h:mm:ss or similar

Revised

If you have minutes as a number in A1 and seconds as a number in A2 as per your comment then you can use this formula in A3 to get that converted to a time value

=TIME(0,A1,A2)

Format as [h]:mm:ss and that will give a result like 0:29:56

....or if you want to get "decimal minutes" - e.g. for the same figures that would be 29.93 - then multiply by 1440, i.e.

=TIME(0,A1,A2)*1440

format A3 as number

Answer 2

I suppose your original cell is a text, here is a formula that will return a TIME value within the cell, you can then format it the way you want:

=TIME(0,MID(A1,1,FIND("minutes",A1)-2),MID(A1,FIND(",",A1)+2,FIND("seconds",A1)-14))

and the french version:

=TEMPS(0;STXT(A1;1;CHERCHE("minutes";A1)-2);STXT(A1;CHERCHE(",";A1)+2;CHERCHE("seconds";A1)-14))

Let say you want it only the minutes in a decimal way:

=MID(A1,1,FIND("minutes",A1)-2)+MID(A1,FIND(",",A1)+2,FIND("seconds",A1)-14)/60

and the french version:

=STXT(A1;1;CHERCHE("minutes";A1)-2)+STXT(A1;CHERCHE(",";A1)+2;CHERCHE("seconds";A1)-14)/60

Don't forget to adapt the cell format (right click > Cell format...) to whatever format you expect (datetime or decimal).