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?
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.
