I am looking for simplified formulas if available.
Range A1:A4 = Hours; Range B1:B4 = Minutes. I need to sum both ranges so that I get a xx hours and xx minutes. AutoSum does not work because the “h” and “m” that are added to each range making them nonnumeric values. My solution was: =VALUE(IF(LEN(A1)>2,LEFT(A
1,2),LEFT(A1,1))) –which simply states IF A1 is has a character count greater than 2 return 2 characters from the left, otherwise return only 1 character from the left and make all results numeric values.
The goal is to sum the minutes column keeping them in standard 60 minute increments and adding the overflow to the sum of the hours. E.g. if the hours column = 1 and the minutes column = 90 the final result would be 2 hours and 30 minutes.
I am using Excel 2010
I attached screenshots of the data & formula view as well as the file (.XLSX).
Any suggestions on how I can simplify these formulas? Thanks!
Excel-Data.JPG
Excel-Formulas.JPG
Excel-Example.xlsx
=SUMPRODUCT(SUBSTITUTE(A1:
and in B6 for total remaining minutes
=MOD(SUMPRODUCT(SUBSTITUTE
see attached
regards, barry
27344329.xlsx