Formulas for Calculating Time

Paul Newcome

@Charlene Pons It looks like you may have mistyped a column name (in bold):

=((VALUE(LEFT([End Time]@row, 2) + (VALUE(RIGHT([End Time]@row, 2))/60)) + ([Date]@row - [Start Date]@row) * 24)) - (VALUE(LEFT([Start Time]@row, 2) + (VALUE(RIGHT([Start Time]@row, 2)) / 60)))

Leibel S

@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

@Leibel Shuchat What did you put in the Time Difference column? Just a basic subtraction of the two cells?

I also wonder how it would react to a rather large gap between the two. I can't remember the exact number but (in addition to the number of digits) there is a "highest number" type limit as well. What if one of those calculations exceeds that number? I am going to do some digging and see if I can find it again.

Paul Newcome

@Leibel Shuchat I found this under the #OVERFLOW error help:

For numeric values the range is -9007199254740992 through 9007199254740992.

I haven't quite wrapped my head around the specifics of your solution, so it could be that I am misinterpreting something or worried about something that just wouldn't be logical (such as millions of days before the limit is reached).

Leibel S

@Paul Newcome

Yes, Time Difference column is a subtraction of the two cells.

In regards to the overflow, we are only multiplying the decimals of the difference (the partial day) by 100000000000000. So that should never end up in an #OVERFLOW error.

Paul Newcome

@Leibel Shuchat That's what I was thinking but wasn't 100% sure. Thanks for confirming.

@L_123 Check it out. We now have a much easier solution when using system generated date/time stamps.

L_123

@Paul Newcome & @Leibel Shuchat

That's brilliant. I saw the ratio mentioned before and thought it was incorrect, but didn't have the time to check it to be sure. The rounding makes perfect sense. Thank you!

Leibel S

@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...

SamprasFederer

Hi there,

I'm kinda of new to SmartSheet and formulas, there is a project that I really need to find the time spent on each of the attended fault, I wonder if anyone can help.

Screenshot 2021-11-22 at 7.06.07 PM.png

Ted Sanborn

@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...

Paul Newcome

@Ted Sanborn Try removing one of the closing parenthesis from the end.

Ika619

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.

GarrettDyer

@Paul Newcome I have been reading through multiple threads and still having some trouble with my time calculations. Can you take a look at my sheet here to see if there is anything you can help me with?

Since this is strictly a calculation sheet I am ok with adding helper columns in to do the calcs.

Paul Newcome

@GarrettDyer Are you able to provide more details? How is the sheet supposed to work? What are the formulas you are currently using? How is the sheet supposed to work?

GarrettDyer

@Paul Newcome did the link to the sheet work? I have details in the sheet along with the formula that explains. I thought that might be easier than typing it out in the comment. Let me know if not and I will try to republish the link.