Create a formula based on time

John Littler

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?

Leibel S

@John Littler

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

Leibel S

@John Littler

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

John Littler

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.

John Littler

@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 5hrs-19m has passed??

Thanks again!

justin.stewart

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?

John Littler

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