Formulas for Calculating Time

@L_123@Paul Newcome

thanks for your posts about using the number stored by Smartsheet to pull the time difference. I had not heard of this before and was enlightened by the idea.

I was able to parse the information and break down how Smartsheet stores the information.

To begin:

=modified@row - Created@row will return the number of days with the decimals for the partial day.

Every day has 86,400 seconds. if you divide 1/86400 you would get 0.0000115740740740741.

However, Smartsheet only works with MAX 5 decimals which is why trying to multiply and divide the number returned by the above formula to get your time would not be accurate because it is rounded.

The way I got around this is by multiplying the number to return all the decimals not just the MAX 5 ones that show in the above formula:

=([Time Difference]@row - ROUNDDOWN([Time Difference]@row, 0)) * 100000000000000

Once we have all the full number we can then divide it by 1157407407.40741 to get the qty of seconds into the day.

You can then use the value returned from the above to return the hours, minutes, and seconds in the day:

Hours:

=INT([Total Seconds]@row / 3600) (to return the hours).

Minutes:

=INT(MOD([Total Seconds]@row, 3600) / 60)

Seconds:

=MOD([Total Seconds]@row, 60)

An example of a fully formatted column is:

="D:" + INT([Time Difference]@row) + " H:" + INT([Total Seconds]@row / 3600) + " M:" + INT(MOD([Total Seconds]@row, 3600) / 60) + " S:" + MOD([Total Seconds]@row, 60)

Please let me know if you found the above to be accurate.

Thank you

@Paul Newcome

@L_123

It also seems that the milliseconds are stored as well. Without getting to complicated if you multiply the results of seconds you can then take that number into excel or the like and calculate the milliseconds.

I could not find a way to do it in Smartsheet because of decimal and OVERFLOW limitations.

What triggered me looking into this is because I noticed some the second column sometimes showing 60 seconds (Formula: =MOD([Total Seconds]@row, 60). This is because the actual number was slightly below 60 (hence the milliseconds) but was showing as 60 due to decimal limitations...

@Paul Newcome, I've tried using the formula

=VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) <> 12, IF(FIND("P", Created@row) > 0, 12), IF(FIND("A", Created@row) > 0, -12))

To convert the 12hr format to 24hr from the created column. Once I close the parenthesis on on the value string, it won't let me add the + IF. It just breaks the formula.

Below I'm good when I close the initial string.

As soon as I add the + and type in if, it doesn't allow me to select the IF formula.

anyone that can help me get past this or if there's something better in Dec 2021 please for the sake of my sanity help...

I have a running log in a sheet called SHUTTLE MOVES.

There are 100's of entries added daily by numerous drivers.

The Created field is an automatic date field. (It must be automatic, and cannot be changed to a manually entered date or time field). When the user enters their shuttle move into the smartsheet form, it is automatically timestamped. (Essentially, it is a unique field, unless 2 drivers just happen to press their submit button at precisely the same moment, which hasn't happened yet).

The driver also enters his/her name on each entry.

In my screenshot, I used a filter to see only today's entries for all drivers. However, in the sheet, it is a running log with thousands of entries for all dates/times. (I have no clue how many entries there will be in a day, so I cannot refer to a specific cell address in a function. It must be a flexible reference).

I would like to tell smartsheet to look at the Created column, determine the first entry for today, determine the last entry for today, and calculate the number of hours between the first and last entry per driver.

I'm not really sure how to do this with both a date and time captured inside of the same cell.

Maybe I need to convert the contents of the cell to a number and find the difference? I'm really not sure.

Thanks for the assistance.