1

How can I convert time entered as text to hours and minutes in Excel?  For example, 14m  40s.

Our report sends the information over in text format. When copying it into Excel, I am unable to convert it to hours and minutes without manually changing the format to each cell one by one.

I have tried formatting the cells but because there is a "m" and "s" listed in the cell it will not convert. I have tried Data → Text to columns, but came to a dead end.

When trying to issue our agent metrics and averages for the week, there is simply not enough time to manually enter in each one to the correct format.

Improve this question

1 Answer 1

Reset to default
4

With data in A1, in B1 enter:

=TIMEVALUE("00:" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"m",""),"s","")," ",":"))

and apply the proper format:

enter image description here

EDIT#1:

If you have only seconds in a cell like:

28s

Then I would use:

=--SUBSTITUTE(A1,"s","")/86400
Improve this answer
2
  • Thank you so much!!! we have one other field with data that only provides the seconds (6s), how would the formula change? We have very few and far days in between where our hold time goes beyond a minute.
    – Tanisha
    Commented May 30, 2017 at 21:45
  • 1
    @Tanisha See my EDIT#1
    – Gary's Student
    Commented May 30, 2017 at 22:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .