Forum Discussion
Solved
Need help in modifying the time column
Hi everyone I'm trying to solve a problem with a time column which I believe is formatted as text in excel column. It has data for minutes and seconds in a single column as shown in the snapshot below. Can someone please help me solve this problem? I want the column to have data in seconds. If anyone could bring out a solution for this, it would be great.
I am attaching the snapshot below for reference.
Thanks in advance
Satishbadiger3 That could be:
=LET( minpos,IFERROR(FIND("m",J2),0), IFERROR(LEFT(J2,minpos-1)*60,0)+MID(J2,minpos+1,LEN(J2)-minpos-1) )You'll note that you define the finding of "m" once, give it a meaningful name like "minpos" and then use that name in the formula.
If your Excel supports LET, you could use the new TEXT functions as suggested by MindreVetande , but the formula will not be much shorter.
Satishbadiger3 Perhaps not the most elegant solution, but a quick-and-dirty one.
=IFERROR(LEFT(J2,IFERROR(FIND("m",J2),0)-1)*60,0)+MID(J2,IFERROR(FIND("m",J2),0)+1,LEN(J2)-IFERROR(FIND("m",J2),0)-1)Quite a bit of repetition that you could eliminate if your Excel version supports the LET function.
Riny_van_Eekelen fantastic, this works!! Thanks a ton
Can you also give out the formula using LET, it would be great if you could do the needful.Satishbadiger3 That could be:
=LET( minpos,IFERROR(FIND("m",J2),0), IFERROR(LEFT(J2,minpos-1)*60,0)+MID(J2,minpos+1,LEN(J2)-minpos-1) )You'll note that you define the finding of "m" once, give it a meaningful name like "minpos" and then use that name in the formula.
If your Excel supports LET, you could use the new TEXT functions as suggested by MindreVetande , but the formula will not be much shorter.
