Create a formula based on time
Good day...I am wondering if there is a formula that can bring back results based on time for example today()created row brings back days...can I bring back hours or minutes?
Best Answer

Not exactly sure what you are looking for but something that can get you started is the below formula which when used on the created or modified columns in Smartsheet will return a number starting from 12:00 AM = 1 through till 11:59 PM = 1440. You can then use these numbers for any calculations...
=IF(SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row)  9), FIND(":", RIGHT(Created@row, LEN(Created@row)  9))  1))) = 12, 0, SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row)  9), FIND(":", RIGHT(Created@row, LEN(Created@row)  9))  1)) * 60)) + SUM(VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2))) + IF(RIGHT(Created@row, 2) = "PM", 720, 0) + 1
Answers

Not exactly sure what you are looking for but something that can get you started is the below formula which when used on the created or modified columns in Smartsheet will return a number starting from 12:00 AM = 1 through till 11:59 PM = 1440. You can then use these numbers for any calculations...
=IF(SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row)  9), FIND(":", RIGHT(Created@row, LEN(Created@row)  9))  1))) = 12, 0, SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row)  9), FIND(":", RIGHT(Created@row, LEN(Created@row)  9))  1)) * 60)) + SUM(VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2))) + IF(RIGHT(Created@row, 2) = "PM", 720, 0) + 1

Thanks for the response, we will look at this and let you the outcome... for background, we are a manufacturing facility and we're trying to bring back production results from each shift however both afternoons and midnights pass over the 12:00am time and need a way to separate them.

@Leibel S so your formula worked as requested thanks so much! Although when you open the door another request always comes up.
Can you calculate how much time has passed between current time and the created time stamp? For example if the time stamp is 01/18/21 9:06 AM and now its 01/18/21 2:25 PM can you calc to show 5hrs19m has passed??
Thanks again!

John Littler,
I am also looking for help on your last comment regarding time passed between a created time stamp and a modified time stamp. Did you ever come up with anything?

Hi Justin,
No we never found a solution and the request / need was not a priority in the end so we moved on sorry!
John
Help Article Resources
Categories
Check out the Formula Handbook template!