Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Hours Calculation

Options
Dominic Chan
Dominic Chan ✭✭
edited 12/09/19 in Archived 2016 Posts

Is there any way to calculate the difference between two differente times, like:

 

Column 01 ==> StartTime: 11:00

Column 02 ==> EndTime: 12:45

Column 03 ==> Hours: 1,75

 

Thank you

 

Tags:
· Share on FacebookShare on Twitter

Comments

  • Jenny Lam
    Jenny Lam ✭
    Options

    Please try this (and replace "r" with your row number):

     

    =VALUE(LEFT([Column02]r,(FIND(":",[Column02]r)-1)))-VALUE(LEFT([Column01]r, (FIND(":",[Column01]r)-1)))+(VALUE(RIGHT([Column02]r,(FIND(":",[Column02]r)-1)))-VALUE(RIGHT([Column01]r,(FIND(":",[Column01]r)-1))))/60

     

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee
    edited 03/18/16
    Options

    Here is the formula I use to calculate time between two given times. This formula looks intimidating (it is!) but it works well. It will require you to designate pm and am to give you the correct duration. 

     

    Rather than 1.75 format, this will give you the exact hour and minute as a result: 1:45

     

    To use it, copy and paste it into a text editor and use FIND & REPLACE to add your cell references to it. 

     

    =INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) + ":" + IF(((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) - INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60) < 10, "0") + ((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) - INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60)

     

    Here's a sheet with an example of how it works:

     

    https://app.smartsheet.com/b/publish?EQBCT=a820445951964445a795591bee66e3d1

     

    Here is another sheet with this formula, but I also stripped out hours and minutes from a list of times and calculated the total (in x.xx format):

     

    https://app.smartsheet.com/b/publish?EQBCT=e27c8cb197874ab8a05593937251c047

    · Share on FacebookShare on Twitter
  • Jenny Lam
    Jenny Lam ✭
    Options

    Here's a sheet with some examples using the forumla I posted earlier. I have also added the formula for row #1 in the discussion so you can copy and try on your sheet:

    https://app.smartsheet.com/b/publish?EQBCT=eaa5d1232e964a18b7796be39a29d41c

     

    · Share on FacebookShare on Twitter
  • Dominic Chan
    Dominic Chan ✭✭
    Options

    Thank you Jenny and Travis ! All options worked very well ! Laughing

    · Share on FacebookShare on Twitter
  • Jemmarie
    Jemmarie ✭✭✭
    Options

    Hello! I am new to SmartSheet, so I apologize and appreciate everyone's patience. I have tried both of the above equations and I keep getting "#UNPARSEABLE". I am sure this is probably something very simple that I am missing/misunderstanding, but I would really appreciate the help.

    https://app.smartsheet.com/b/publish?EQBCT=f2c5a0ba9b4f48748bba7e46a81fcef8

    Column 2 is labeled: (Day1)StartTime

    Column 3 is labeled: (Day1)EndTime

    I am trying to calculate the duration between the two times into Column 4. 

    Thank you!

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories