Working with Time Values in Cells
Hi all, perhaps someone can help me with a workaround. I have a sheet that captures time values in a column. As far as I know, Smartsheet doesn't recognize time or have any formulas more granular than "Date" formulas. My question is, does anyone know a way to convert time values into numeric values? For example, if the value in the cell is "1:30" (1 minute and 30 seconds), I want to use a formula to output "1.5" (one and a half minutes). This way I can run averages and graphs based on these time figures.
I tried using the RIGHT formula: =RIGHT(Time@row,2)/60 but this doesn't work because it's a text formula and the numbers that are output are being read as text. So with this formula, my formula would give me the isolated 30 that i'm looking for, but when I try to divide that by 60, I get an error because it thinks I'm trying to divide text by numbers.
Has anyone else encountered this problem?
Thanks,
Don
Comments
-
You are actually pretty close. Just wrap the RIGHT function in a VALUE function to convert it back to a number instead of text.
=VALUE(RIGHT(Time@row,2))/60
EDIT:
To convert the entire 1:30 into a decimal number, I would actually suggest something along the lines of...
=VALUE(LEFT(Time@row, FIND(":", Time@row) - 1) + VALUE(RIGHT(Time@row,2))/60
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Brilliant. That's exactly what I was looking for. I didn't know about the value function. I also haven't used find before either, so thanks for putting all that together! This is a huge help.
-
Happy to help!
Find is a great tool, and I use it in many different ways in many different applications.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!