Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Is there a way to add hours and minutes in a column?
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?
Comments
-
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.
