Sign in to join the conversation:
The posts in this section are historical and no longer monitored for accuracy. If this discussion interests you and you'd like to join in, please visit the active Community to post and engage.
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.
