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!
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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
1215
2: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
42215
2: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,099,699
Messages
5,470,274
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top