#### 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
edited 12/09/19

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:

• Options

=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

• 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

• 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

• Options

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

• ✭✭✭
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!

This discussion has been closed.