Solved

Convert to time

Posted on 2014-10-16
4
89 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 51

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 51

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Dynamic Chart Title Not Working 21 199
macro to compare 2 Excel columns. 14 41
Outlook 2016 will not send or receive my Google Apps mail 4 48
Custom fill series 12 38
You can provide a virtual interface for remote stakeholders in a SWOT analysis through a Google Drawing template. By making real time viewing and collaboration possible, your team can build a stronger product.
Google is more than just a search engine. Over the years the company has developed a wide range of online services that are readily available to all users. This article highlights how one can use Google services for simple project management.
This Micro Tutorial  demonstrates whether your site uses one subdomain or multiple subdomains, how to create full URLs from Google Analytics content reports. This procedure is called concatenation and can also be done with the CONCATENTATE function.…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

739 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