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.
As in the provided image, I want the sum total amount of Log Work (Column C) for Matthew R. on the 17th of October.
CodePudding user response：
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.