LinkBack URL
About LinkBacks
I can't find a formula to encompass the formats my data is in. Ex:
1h 17m
21 minutes
14 minutes
1h 36m
22 minutes
57 minutes
1 hour
Sample workbook is attached. Any help would be greatly appreciated!
I can't find a formula to encompass the formats my data is in. Ex:
1h 17m
21 minutes
14 minutes
1h 36m
22 minutes
57 minutes
1 hour
Sample workbook is attached. Any help would be greatly appreciated!
Last edited by sick stigma; 06-29-2021 at 12:33 PM.
One way:Formula:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, " hour", "h"), " minutes", "m"), "m", ":00"), "h ", ":"), "h", ":00:00")*24*IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, " hour", "h"), " minutes", "m"), "m", ":00"), "h ", ":"), "h", ":00:00"))<=5, 1, 60)
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Alternative:
=IFERROR(AGGREGATE(14,6,RIGHT(LEFT("000"&SUBSTITUTE(A2," ","")&"hm",SEARCH("h","000"&SUBSTITUTE(A2," ","")&"hm")-1),{1,2,3})+0,1)/24,0)+ IFERROR(AGGREGATE(14,6,RIGHT(LEFT("000"&SUBSTITUTE(A2," ","")&"hm",SEARCH("m","000"&SUBSTITUTE(A2," ","")&"hm")-1),{1,2,3})+0,1)/1440,0)
Quang PT
Or try:
=IFERROR(LEFT(A2,FIND("h",A2)-1)*60,0)+IFERROR(LOOKUP(61,--MID(A2,FIND("m",A2)-{1,2,3},{1,2,3})),0)
@bebo: doesn't seem to work
@Phuocam: much shorter/neater than mine
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks