Is there a way to add hours and minutes in a column?

Cara Rodriguez

This is the only thing holding us back form using smartsheets. Is there a way to add columns such as the one in the attached picture and come out with the correct answer for hours and minutes?

Rob Hagan

Hi Cara,

 

I assume that the column in your picture is of format Text/Number (and let's call that column Time).

 

If so, I suggest that you put this formula in row 1 of a new column (called Hours and of format Time/Number):

=IF(FIND(":", Time1) = 0, "Bad time format", LEFT(Time1, FIND(":", Time1) - 1))

 

and this formula in row 1 of another column (called Minutes and of format Time/Number):

=IF(FIND(":", Time1) = 0, "Bad time format", MID(Time1, FIND(":", Time1) + 1, 99))

 

You then copy these new formulas down beside your Time data.

 

The new formulas each first test to see if there is a colon character and if there is then they return either the characters before the colon (the hours) or the characters after the colon (the minutes).

 

Cheers,

 

Rob.