Hi all,
Any ideas on how to solve this issue?
I've got a series of strings with measures of time, all of them with the following structure:
48 D 21 H 21 M
each string is returned in a single cell
Any suggestions on how to convert this info into a single value that could be either days or hours? not manually of course :-)
Thanks!
whar does 1 day 3 hrs 5 mins look like ?
Last edited by martindwilson; 07-16-2012 at 09:27 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
It will look like:
1 D 3 H 5 M
(thanks for looking into it)
If you convert it into a date format, what is the beginning date? the default would be 48 days from 1/1/1900
totaly wrong answer
Last edited by martindwilson; 07-16-2012 at 09:36 AM.
I tried the formula above and I got the #VALUE! error
(I replaced a1 with the relevant cell where my string is located)
If I apply a date format the string does nto change...
will give decimal hoursFormula:
Please Login or Register to view this content.
48 D 21 H 21 M 1173.014583
1 D 3 H 59 M 27.04097222
=left($a$1,find("d",$a$1)-1)+(mid($a$1,find("d",$a$1)+1,find("h",$a$1)-find("d",$a$1)-2)+mid($a$1,find("h",$a$1)+1,find("m",$a$1)-find("h",$a$1)-2)/60)/24
martindwilson solution worked like a charm!
(stunn soution give the same error as martin's first try)
Tlanks a lot for your help solving the issue
For 48 D 21 H 21 M my solution gives 48.88958333, Martin's gives 1173.015
For 1 D 3 H 5 M my solution gives 1.12847222, Martin's gives 27.00347
Mine is in days, Martin's is almost in hours.
Hours would actually be: 1173.35 and 27.08333
If mine is giving you an error, perhaps you have multiple spaces between letters and numbers?
Last edited by stunn; 07-16-2012 at 10:12 AM.
You can test this by typing =48+time(21,21,0) and formatting the cell as numeric to see the answer in days.
=(48+TIME(21,21,0))*24 shows it in hours.
Try this instead:
=TRIM(LEFT($A1,FIND("D",$A1)-1))+(TRIM(MID($A1,FIND("D",$A1)+1,3))+TRIM(MID($A1,FIND("H",$A1)+1,3))/60)/24
However, I should note that this could cause more problems than it solves if you do have multiple spaces. Simply because it only looks at the first three characters after D and H. So, although "48 D 21 H 21 M" would still work, "48 D 21 H 21 M" would not.
Possibly the safest option would be:
=TRIM(LEFT($A1,FIND("D",$A1)-1))+(TRIM(MID($A1,FIND("D",$A1)+1,FIND("H",$A1)-FIND("D",$A1)-1))+TRIM(MID($A1,FIND("H",$A1)+1,FIND("M",$A1)-FIND("H",$A1)-1))/60)/24
Last edited by stunn; 07-16-2012 at 10:29 AM.
It looks like the forum editor automatically removed the multiple spaces from my examples! I'll use ^ instead. "48^D^21^^H^21^^M" would work, and "48^D^^21^H^^21^M" would not.
yep mine is wrong it should give decimal of hours not days so 5 minutes = 5/60
=TRIM(LEFT($A1,FIND("D",$A1)-1))*24+TRIM(MID($A1,FIND("D",$A1)+1,3))+TRIM(MID($A1,FIND("H",$A1)+1,3))/60
48 D 21 H 30 1173.5
1 D 3 H 59 M 27.98333333
mind you
48^D^21^^H^21^^M" would work, and "48^D^^21^H^^21^M"
why would there be more than 2 digits in the hours slot?
Last edited by martindwilson; 07-16-2012 at 04:54 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks