Forum Discussion
Solved
Building a formula for Hours and minutes needing an IF Statement - probably
The information is being exported to Excel from another platform. Last Name First Name Hours/Quantity Minutes Smith Jane 1 hr. 30min Smith John 7 hr. McDonald Ron 30min...
=LET(hpos, SEARCH("hr.", C2), hours, IF(ISNUMBER(hpos), LEFT(C2, hpos-1), 0), mpos, SEARCH("min", C2), minutes, IF(ISNUMBER(mpos), MID(C2, mpos-2, 2), 0), 60*hours+minutes)
Hi!
As variant:
=IF(ISNUMBER(SEARCH("hr",D4)),(LEFT(D4,FIND("hr",D4)-1)*60)+IFERROR(MID(D4,SEARCH(" ",D4,SEARCH(" ", D4) + 1),3),0),LEFT(D4,FIND("min",D4)-1))
This formulation takes into account that hours are being written EXACTLY like " 1 hr. 30min" for times with complete hours and "30min" with times with no complete hours.