Time Indication and DATEONLY function

therapy_mindset

Hello,

I have a few questions below regarding Smartsheet:

1. 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.
2. 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?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/327756#Comment_327756

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.

Jeff Reisman

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)

therapy_mindset

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.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/327756#Comment_327756

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.