Time Indication and DATEONLY function
Hello,
I have a few questions below regarding Smartsheet:
- We're utilizing Smartsheet to track start time and end time in a 24-hour clock to count the duration of some of the tasks we complete.
- How can I remove the time in "Created", I would only like to have the date. (photo 2)
Question 1 Explanation below:
I've noticed some of our front line staff will input times like "0412 or 0420" indicating 4:20 am. However, the Smartsheet won't register this number as 0412 or 0420 but as 412 or 420. Is there a way to make sure the time shows 0412 or 0420 and not 412 or 420?
Best Answer
-
It's a trade off. It can be a number value and validated as such, or not, in which case text can be entered.
Make sure your Date column is a Date-type column. The formula works, but only if the the column type is set to Date.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
Question 1 - It looks like on your Start Time field, you have "number only" validation turned on. Take that validation off and if they enter "0420", it saves as a text formatted value "0420".
That being said, if you want to do any math with those times, you either need to save them as numbers, or use the VALUE function inside any other formulas to convert them to numeric values on the fly.
Question 2 - You can't present the system column "Created" with only the date portion. You need to use a helper date-type column to do that, using the DATEONLY function:
=DATEONLY(Created@row)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hello Jeff,
I've removed the "Number Only" and made it "No validation". But, I also want to make sure that the staff don't input any letters incorrectly. How can I do this?
For question 2... I've done this but got an error formula.
-
It's a trade off. It can be a number value and validated as such, or not, in which case text can be entered.
Make sure your Date column is a Date-type column. The formula works, but only if the the column type is set to Date.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
