Building a formula for Hours and minutes needing an IF Statement - probably

[Stephen Morley]

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

Can anyone provide assistance to help me create the information needed in minutes?

[#2 · Accepted solution]

@Stephen Morley 

=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)

[#3]

@Stephen Morley 

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.