Solved

Convert to time

Posted on 2014-10-16
4
84 Views
Last Modified: 2014-11-12
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?
0
Comment
Question by:aideb
  • 2
  • 2
4 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40386036
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
 
LVL 2

Author Comment

by:aideb
ID: 40386121
Hi,

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

https://docs.google.com/spreadsheets/d/1MwHV93h6Ne0qAXECucq4caqJ7X393Wm5q8vZMSvSrvo/edit?usp=sharing

Thanks
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40389884
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
 
LVL 2

Author Closing Comment

by:aideb
ID: 40391898
Works a treat! Thanks again.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Challenge I was developing a new website for a client the other week in WordPress and they asked me if it was possible to track the clicks of the sponsor images they include on their home page. My first port of call was to use the Yoast Google …
One of my favorite tools to use with Google Drive is the offline access. Setting up offline access for Google Drive makes it easier for users to edit and view their docs, sheets and slides without Internet connection. Follow these steps to learn how…
This Micro Tutorial will demonstrate how marketers can use the Mobile Emulation Tool in Chrome Developer Tool. This will let you preview your site on any mobile device.
Shows how to create a shortcut to site-search Experts Exchange using Google in the Chrome browser. This eliminates the need to type out site:experts-exchange.com whenever you want to search the site. Launch the Search Engine Menu: In chrome, via you…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question