Formulas for Calculating Time

12345679Next

Comments

  • Paul NewcomePaul 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)))

    thinkspi.com

  • Leibel ShuchatLeibel Shuchat ✭✭✭✭

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

    [email protected] - [email protected] 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 NewcomePaul 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.

    thinkspi.com

  • Paul NewcomePaul 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).

    thinkspi.com

  • Leibel ShuchatLeibel Shuchat ✭✭✭✭

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

    thinkspi.com

  • L_123L_123 ✭✭✭✭✭
    edited 11/18/21

    @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 ShuchatLeibel Shuchat ✭✭✭✭

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

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


  • @Paul Newcome, I've tried using the formula

    =VALUE(MID([email protected], FIND(" ", [email protected]) + 1, FIND(":", [email protected]) - (FIND(" ", [email protected]) + 1))) + IF(VALUE(MID([email protected], FIND(" ", [email protected]) + 1, FIND(":", [email protected]) - (FIND(" ", [email protected]) + 1))) <> 12, IF(FIND("P", [email protected]) > 0, 12), IF(FIND("A", [email protected]) > 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 NewcomePaul Newcome ✭✭✭✭✭

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

    thinkspi.com

Sign In or Register to comment.