Search code examples
excelif-statementexcel-formulasumformula

Sum the values of a cell in Excel or change to a different expression of that value in another cell

I have a cell of a worksheet with a value of

1h 25m

entered with the cell (got it from a Jira import). I am trying to figure out how to populate another cell on the same row with a purely numerical version of this cell value, i.e.

85

This is so that I can run SUMIF statements to get the total amounts of minutes for a given person, on a given day. enter image description here

As in the provided image, I want the sum total amount of Log Work (Column C) for Matthew R. on the 17th of October.

Solution

  • Here, we're creating two helper columns. You could combine everything, but thought this may be easier to illustrate.

    Value hours minutes sum
    5m 0 5 5
    1h 35m 1 35 95
    3h 3 0 180
    4h 55m 4 55 295
    12h 12 0 720

    Column A (Value) is your original value.

    Column B (hours) is the following formula, which finds the letter "h" and returns everything to the left of it. If H is not found then return a 0.

    =IFERROR(LEFT(A2,FIND("h",A2)-1),0)

    Column C (minutes) is the following formula, which returns everything to the right of "h" and then removes "m" and trims it. But if "m" is not found then return a 0.

    =IF(ISNUMBER(SEARCH("m",A2)),TRIM(SUBSTITUTE(IFERROR(RIGHT(A2,LEN(A2)-FIND("h",A2)),A2),"m","")),0)

    Lastly, column D simply calculates total minutes as:

    =(B2*60)+C2

    You can probably shorten column C's formula, but this at least provides you an interim solution.