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
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
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
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
Thank you Jenny and Travis ! All options worked very well !
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!
