To keep it short and sweet, we have a column like this:
5 mins
1 min
1 hr, 45 mins
3 hrs
1 hr
6 hrs, 1 min
and we want the output in h:mm duration format, like:
0:05
0:01
1:45
3:00
1:00
6:01
How do we do it with a formula?
To keep it short and sweet, we have a column like this:
5 mins
1 min
1 hr, 45 mins
3 hrs
1 hr
6 hrs, 1 min
and we want the output in h:mm duration format, like:
0:05
0:01
1:45
3:00
1:00
6:01
How do we do it with a formula?
B1 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(FIND("min",IF(ISERROR(FIND("hr",C1,1)),"0 hr, " &A1,A1))),IF(ISERROR(FIND("hr",C1,1)),"0 hr, " &A1,A1)&", 0 min",IF(ISERROR(FIND("hr",C1,1)),"0 hr, " &A1,A1)),"min",""),"s","")," hr, ",":")*1
Format custom hh:mm
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Sorrry, sucuri firewall did not let me write the code, so find it in file attach.
Quang PT
OK, so here it is:
=TIMEVALUE(IF(IFERROR(FIND(":",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@String]," ",""),"min",""),"hr",""),"s",""),",",":")),0)=0,"0:"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@String]," ",""),"min",""),"hr",""),"s",""),",",":"),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@String]," ",""),"min",""),"hr",""),"s",""),",",":")))
Yeah, right!
See the attached spreadsheet to understand how I got there.
Colum A is String. This is the string as copied from your post.
Column B is String 1 which basically takes out min and mins, hr and hrs and substitutes a colon for the comma so the resulting string looks almost like a string for time.
Column C is String 2 which is either zero if string 1 does not have a colon or zero or some other number if String 1 does have a colon. The find command is one way to test if a string has a certain substring.
Column D is String 3 the formula here puts "0:" in front of String 1 when it needs it (in other words only minutes are in the original string).
Column E is Time which is the TIMEVALUE of string 3.
Colum F is the same formula as column E except that the pieces are successively substituted back until everything is expressed only in terms of Column A (The original string)
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Try
=SUMPRODUCT(TEXT(MID(0&A2,FIND({"h","m"},0&A2&"hm")-2,2),"0;;;\0")/{24,1440})
While I like a few of the solutions - this is likely what I would utilize as a ripper value off of text... This assumes that you are consistent in formatting if there is no hour then just the minute is placed and if there is an hour it is written followed by a comma... this comma is the guide to the formula below... with or without it dictates if there are hours to be evaluated... so if your format is that in which you shared then this should work great.
Please Login or Register to view this content.
-If you think you are done, Start over - ELeGault
Thanks mehmetcik, but I'm getting this instead of the desired output:
00:05
00:01
00:01
00:03
00:01
00:06
--
Thanks bebo021999, your formula works like a charm..
Here is it again:
--=MID("00hr00"&SUBSTITUTE(SUBSTITUTE(A1," ",""),",","00")&"000m",IFERROR(SEARCH("hr","00hr00"&SUBSTITUTE(SUBSTITUTE(A1," ",""),",","00")&"000m",SEARCH("hr","00hr00"&SUBSTITUTE(SUBSTITUTE(A1," ",""),",","00")&"000m")+1),SEARCH("hr","00hr00"&SUBSTITUTE(SUBSTITUTE(A1," ",""),",","00")&"000m"))-2,2)/24+MID("00hr00"&SUBSTITUTE(SUBSTITUTE(A1," ",""),",","00")&"000m",SEARCH("m","00hr00"&SUBSTITUTE(SUBSTITUTE(A1," ",""),",","00")&"000m")-3,3)/1440
Thanks dflak for the detailed reply.
Close enough, but getting this instead of the desired output:
0:05
0:01
1:45
0:03
0:01
6:01
(4 and 5 are wrong, as they're showing as minutes.)
--
Thanks Bo_Ry, close enough but the 3rd sample is giving 1:05 instead of 1:45
Last edited by nicoan; 11-26-2021 at 02:08 PM.
If your format stays like your example... this should be golden for you...
Please Login or Register to view this content.
Try again
=SUMPRODUCT(TEXT(MID(0&A2,FIND({"h","m"},0&A2&"hm")-3,3),"0;;;\0")/{24,1440})
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks