[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Convert to time

Posted on 2014-10-16
4
Medium Priority
?
110 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 54

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 54

Accepted Solution

by:
Rgonzo1971 earned 2000 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Google Forms is a great platform to easily create polls, questionnaires and surveys. The service allows you to receive data in real-time and is a simple way to conduct large-scale surveys that can be distributed to multiple people.   The platform …
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will introduce a Google tool, which is a great way to learn more about dimensions in metrics in Google Analytics, even if you use the interface or the API.
This Micro Tutorial will demonstrate common damaging and frequent mistakes I see in most analytic audits. Most of them are campaign tagging mistakes, so this video will break it down into simple steps.

640 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