# Convert to time

I am trying to convert the times from text

e.g.
1m 5s left
4m 30s left
13h 4m left
14h 20m left
14h 35m left
14h 40m left
3d 14h left

There is likely to be a clever way to do this....Can anyone help?
LVL 2
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi,

You could use the split Function

=Split(A1, " ") to separate the elements
then
read the characters before the letter and mutiply by the number by the time it represents
=RIGHT(B1,LEN(B1)-1)*(IF(LEFT(B1,1)="d",1)+IF(LEFT(B1,1)="h",TIME(1,0,0))+IF(LEFT(B1,1)="m",TIME(0,1,0))+IF(LEFT(B1,1)="s",TIME(0,0,1)))

and the add all the partial results in one cell

Regards
0
Author Commented:
Hi,

I am having some issues with getting this to work. Can you take a look and see whats wrong?

Thanks
0
Commented:
Hi,

in E1

=(left(B1,Len(B1)-1))*(IF(right(B1,1)=ʺdʺ,1)+IF(righT(B1,1)=ʺhʺ,TIME(1,0,0))+IF(righT(B1,1)=ʺmʺ,TIME(0,1,0))+IF(RIGHT(B1,1)=ʺsʺ,TIME(0,0,1)))

in F1

=(1*left(C1,Len(C1)-1))*(IF(right(C1,1)=ʺdʺ,1)+IF(righT(C1,1)=ʺhʺ,TIME(1,0,0))+IF(righT(C1,1)=ʺmʺ,TIME(0,1,0))+IF(RIGHT(C1,1)=ʺsʺ,TIME(0,0,1)))

and in G1

= F1+G1

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Works a treat! Thanks again.
0