Forum Discussion

Holmyjj42's avatar
Holmyjj42
Copper Contributor
Oct 16, 2022

Time conversion formula help

I am trying to convert a standard time sum formula from h:mm to h+mm and no matter how I input the hour won’t carry over to add the sum but the minutes do. I feel as though it is a simple fix but I a...
excel
Formulas and Functions
Harun24HR's avatar
Harun24HR
Silver Contributor
Oct 16, 2022
Show your input and desired output.
Holmyjj42's avatar
Holmyjj42
Copper Contributor
Oct 16, 2022

Harun24HR sorry I guess I should’ve included an example. Say I have multiple “training times in an excel tracker such as 1:45 = an hour and 45 minutes. I would like the formula to accept 1+45 and add the sum off all different times. So 1:30 twice is 3:00 and I’m just trying to get to a point where I input 1+30 and 1+30= 3 in my sum box. Essentially just replacing the colon with the plus sign. No matter what I do it won’t accept the hour number in front of the + but it accepts the minutes behind the + sign. So if I input 1+00 it won’t register but if I input +45 it accepts the 45 into the formula and shows +45. 

  • Harun24HR's avatar
    Harun24HR
    Silver Contributor
    Oct 16, 2022

    Holmyjj42 Give a try on below formula-

    =LET(x,TEXTSPLIT(TEXTAFTER(FORMULATEXT(A1),"="),"+"),TIME(INDEX(x,1,1),INDEX(x,1,2),0))

    See the attached file.