Solved

Convert to time

Posted on 2014-10-16
4
86 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 50

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 50

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel sheet question 12 55
Finding a closest match in Excel 7 74
Database needed for HR files 4 77
what is the best open source database solution? 3 70
Cloud file services can fill many different roles for your business. Often, the use of cloud file services begins with employees using consumer products, like Dropbox, to share files with customers and each other. While sync-and-share can be an effe…
With voice direction tools, various map viewing styles, the ability to get directions for different means of transportation and great service, Google Maps is simply one of the best navigations app in the market.    However, over the past year, an …
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.
This Micro Tutorial will demonstrate without sampling how to find out top organic landing pages. The hack gets around the standard way to find the pages in Google Analytics results in sampling for larger sites.

685 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