Convert 215 to 2:15

uskafb

New Member
Joined
Mar 8, 2010
Messages
1
When I export data from Google Analytics into Excel , the minutes and seconds are converted into a number, .e.g 2:15 > 215. How do I convert the number back to minutes and seconds or simply insert a colon? I manually changed 215 to 135 seconds, divided by 60 and formatted the cell but that seems like the hard way. Can't this be automated? Thanks for your help!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

albasheer

Board Regular
Joined
Dec 14, 2009
Messages
159
Welcome to the forum.
Try this:

=TIMEVALUE("00"&":"&MID(A1,1,LEN(A1)-2)&":"&RIGHT(A1,2))
 

meldoc

Well-known Member
Joined
Jul 18, 2009
Messages
1,249
Hello and welcome

Try.

Excel Workbook
AB
12152:15
Sheet1


And format as h:mm.
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
Welcome to MrExcel board. . .

you could do something like this and copy the formula down
Excel Workbook
EF
422152:15
Sheet1
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
If you want to convert to minutes and seconds (rather than hours and minutes) then a revision to meldoc's suggestion:

=TEXT(A1,"0\:00\:00")+0

format result cell as m:ss
 

Watch MrExcel Video

Forum statistics

Threads
1,132,901
Messages
5,655,872
Members
418,249
Latest member
JOYADA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top