Convert 215 to 2:15

[uskafb]

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!

 

[albasheer]

Welcome to the forum.
Try this:

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

 

[meldoc]

Hello and welcome

Try.

Excel Workbook
	A	B
1	215	2:15
Sheet1

And format as h:mm.

 

[texasalynn]

Welcome to MrExcel board. . .

you could do something like this and copy the formula down

Excel Workbook
	E	F
42	215	2:15
Sheet1

 

[barry houdini]

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

 

[meldoc]

Yes sorry I misread that, ,thank you Barry.