Time in 24 hours

Tori Peregoy

Please help! 

I am working on a IT Change Request sheet where the request is submitted by Forms. For a change to take place we require a specific time to be entered. I know Smartsheet currently does not have a time option, but is it possible to have the Implementation Time, whether it's entered as AM or PM or in a 24 hour format, all be converted into a 24 hour format only? 

I am looking for a solution so I'm able to sort it by the date and then by the Implementation Time. In the example, currently it's not being sorted correctly when it comes to the Time. It's very important to get this column to sort correctly. Any help would greatly be appreciated. Even if I have to create a hidden column so it converts the time into a decimal so I'm able to get it to sort properly is fine too. 

Thanks again! 

Mike Wilday

You could split the Implementation time by the colon and create a hidden hour, and minute column. Then you could sort by Date, Hour, and Minute. 

Use this for the Hour Column:

• =LEFT([Implementation Time]@row, FIND(":", [Implementation Time]@row) - 1)

Use this for the Minutes Column:

• =RIGHT([Implementation Time]@row, FIND(":", [Implementation Time]@row) - 1)

Ezra

To piggyback on Mike's suggestion, if the people aren't comfortable with 24 hour time, make a dropdown for AM/PM. Then add 12 to the hidden hour column..

+IF9[AM PM]@row="PM",12,0)

On a side note, I keep hearing that there will be a Time implementation soon... just don't know when.

Mike Wilday

I am hearing the same thing, time workflow and functions are coming someday.  

Tori Peregoy

Thank you so much for the input. I really appreciate the help. 

I tried adding the +IF([AM PM]@row="PM",12,0) to the hour column, but it just adds 12 to the end of the number. Ex. 8 ends up being 812.

Any other thoughts so it adds 12 to the 8 if the AM PM column states PM?

Thanks again! 

Ezra

Aaaaack.. yeah. Smartsheet uses the "+" symbol to join, instead of the "&" like other spreadsheet programs.

You'll have to....

=SUM([Hour Column]@row,IF([AM PM]@row="PM",12,0))

Paul Newcome

That's probably because the 8 is being stored as a text string. Try wrapping the the LEFT portion in a VALUE function to convert it to a number.

 

=VALUE(LEFT([Implementation Time]@row, FIND(":", [Implementation Time]@row) - 1)) + IF([AM PM]@row = "PM", 12, 0)

 

Tori Peregoy

That worked perfectly! Thank you so much Paul. 

Paul Newcome

Happy to help!