Forum Discussion

SteveMorley1's avatar
SteveMorley1
Brass Contributor
Apr 29, 2024

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

The information is being exported to Excel from another platform.

 

Last NameFirst NameHours/QuantityMinutes
SmithJane1 hr. 30min 
SmithJohn7 hr. 
McDonaldRon30min 

 

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

excel
  • HansVogelaar's avatar
    HansVogelaar
    Apr 29, 2024

    SteveMorley1 

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

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 29, 2024

    SteveMorley1 

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

  • MAngosto's avatar
    MAngosto
    Iron Contributor
    Apr 29, 2024

    SteveMorley1 

     

    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.