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 belo...
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.
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.
- Thank you so much Riny!