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.
Convert Text to Number?
Is there a way in a Smartsheet column to convert a Text value into a Numeric value?
For example, I have a column called "Duration", formatted as a "Text/Number" column type, and I have values in that column that are in the HH:MM:SS format, and look something like this: 00:15:00, 01:20:00, etc.
I'm trying to add a few of these together to calculate the "Total Duration", but there is not a simple way to do this in Smartsheet, so I am attempting to "dissassemble" each cell into parts, then add the parts together, and then "combine" the parts into the HH:MM:SS format.
So far I have been able to "dissassemble" the cell values into three new columns by using the LEFT(), MID(), and RIGHT() functions. This works, but when I do that the resulting cells contain text values not numerical values - which means that the SUM() function does not work.
Any ideas on how to convert the "deconstructed" cells into numerical values?
Thanks for the help!
Regards,
David
Comments
-
Ooops, sent too soon.
I found the VALUE() Function!
Now to figure out the "reconstruction" part....
Thanks,
David
-
Good to hear you figured it out! If your results are in multiple cells, you can reconstruct them by using the concatenate function. (=Cell1 + “:” + Cell2 + “:” + Cell3)
-
VALUE is your friend here. A good trick is to use hidden columns in case you need intermediate steps. Also, this helps if you want to take an iterative approach to your formulas (step by step using columns, then combining the steps into a long formula). Hope this helps.
-
Thanks everyone!
I used all of your suggestiones and got it all working like a charm.
Thanks for the help.
Regards,
David
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives