Time duration
Hello how can I get the number of hours to calculate with a Start Time and and End Time? Want the AM PM format as shown. Thanks for your help!
Answers

Hey @Greg Zinn, I see you're a fairly new member to the community, welcome!
Now that I've buttered you up with a friendly introduction, I hate to say this, but Smartsheet is not friendly with time in cells. There's a lot of posts out there asking to do what you're wanting to do, and they all require workarounds. Smartsheet really ought to have a dedicated "Time" column type, or at the very least, an "Add time" or "Select time" function; but instead, it keeps time as a string. Apparently this was something that was expected at Smartsheet's annual conference in 2020, but didn't.
I started out thinking this couldn't be that hard, but then I realized how deep this rabbit hole goes. My solution is not perfect  this solution will not work for hours between days. That is, if something starts at 9:00pm and ends at 9:00am, this is incapable of calculating that. But, for your purposes, it looks like I've been able to solve it.
Above is a screenshot of what I've made.
Here's a link to a public sheet with the formulas already in it  I believe you can copy it from here.
For anyone who can't access the Sheet for some reason, here's how I did this:
 Created two new columns  "Difference" and "Time Difference".
 "Difference" is the numerical value difference between "End Time" and "Start Time".
 Here's my formula for Difference:
=(IF(FIND(":", [End Time]@row) = 2, VALUE(LEFT([End Time]@row, 1)) + IF(CONTAINS("PM", [End Time]@row), 12, 0), IF(FIND(":", [End Time]@row) = 3, VALUE(LEFT([End Time]@row, 2)) + IF(AND(CONTAINS("12:", [End Time]@row), CONTAINS("PM", [End Time]@row)), 0, IF(CONTAINS("PM", [End Time]@row), 12, 0) ) ) ) + IFERROR(IF(VALUE(MID([End Time]@row, 3, 2)) > 0, VALUE(MID([End Time]@row, 3, 2)) / 60), IFERROR(IF(VALUE(MID([End Time]@row, 4, 2)) > 0, VALUE(MID([End Time]@row, 4, 2)) / 60), 0 ) ) )  (IF(FIND(":", [Start Time]@row) = 2, VALUE(LEFT([Start Time]@row, 1)) + IF(CONTAINS("PM", [Start Time]@row), 12, 0), IF(FIND(":", [Start Time]@row) = 3, VALUE(LEFT([Start Time]@row, 2)) + IF(AND(CONTAINS("12:", [Start Time]@row), CONTAINS("PM", [Start Time]@row)), 0, IF(CONTAINS("PM", [Start Time]@row), 12, 0)))) + IFERROR(IF(VALUE(MID([Start Time]@row, 3, 2)) > 0, VALUE(MID([Start Time]@row, 3, 2)) / 60), IFERROR(IF(VALUE(MID([Start Time]@row, 4, 2)) > 0, VALUE(MID([Start Time]@row, 4, 2)) / 60), 0 ) ) )
 "Time Difference" is the "mm:ss" format to show the duration. So 1.5 hours is "1:30", 0.01667 = "0:01", etc. Here's the formula for Time Difference:
= VALUE( IF( FIND(".", Difference@row) > 0, MID(Difference@row, 1, FIND(".", Difference@row)  1), Difference@row ) ) + ":" + IF( FIND(".", Difference@row) = 0, "00", IF( AND( VALUE(Difference@row) < 0.16666, VALUE(Difference@row) > 0 ), "0" + ROUND(VALUE(IF(FIND(".", Difference@row) > 0, MID(Difference@row, FIND(".", Difference@row), 5), 0)) * 60, 0), ROUND(VALUE(IF(FIND(".", Difference@row) > 0, MID(Difference@row, FIND(".", Difference@row), 5 ), 0 ) ) * 60, 0 ) ) )
Some notes on this:
This works for values across AM and PM (even if you shorthand it and write "8am" or "10pm", and neglecting to write either "AM" or "PM" defaults to "AM") but it does NOT work for values that are "12:00 AM". I'm sure someone could figure out that formula, but for now if you need to use 12:00 AM you can utilize "0:00 AM" and it does the same thing.
Whew. That was something.

If this solved your issue, please mark "Yes" below  it helps the community find solutions and random Googlers out there (you know who you are  upvote! 👁👁)
If this answer answers your question, please press "Yes" above  it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick  Connect with me on LinkedIn.

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1yearold twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

First off WOW.. Thank for you for the detailed analysis!!! Amazing. Do you have a Formula that work as a cut and Paste.. I tried to cut and paste the above but it did not work.

Hey @Greg Zinn, try these since the above didn't work for you:
"Difference" column:
 =(IF(FIND(":", [End Time]@row) = 2, VALUE(LEFT([End Time]@row, 1)) + IF(CONTAINS("PM", [End Time]@row), 12, 0), IF(FIND(":", [End Time]@row) = 3, VALUE(LEFT([End Time]@row, 2)) + IF(AND(CONTAINS("12:", [End Time]@row), CONTAINS("PM", [End Time]@row)), 0, IF(CONTAINS("PM", [End Time]@row), 12, 0)))) + IFERROR(IF(VALUE(MID([End Time]@row, 3, 2)) > 0, VALUE(MID([End Time]@row, 3, 2)) / 60), IFERROR(IF(VALUE(MID([End Time]@row, 4, 2)) > 0, VALUE(MID([End Time]@row, 4, 2)) / 60), 0)))  (IF(FIND(":", [Start Time]@row) = 2, VALUE(LEFT([Start Time]@row, 1)) + IF(CONTAINS("PM", [Start Time]@row), 12, 0), IF(FIND(":", [Start Time]@row) = 3, VALUE(LEFT([Start Time]@row, 2)) + IF(AND(CONTAINS("12:", [Start Time]@row), CONTAINS("PM", [Start Time]@row)), 0, IF(CONTAINS("PM", [Start Time]@row), 12, 0)))) + IFERROR(IF(VALUE(MID([Start Time]@row, 3, 2)) > 0, VALUE(MID([Start Time]@row, 3, 2)) / 60), IFERROR(IF(VALUE(MID([Start Time]@row, 4, 2)) > 0, VALUE(MID([Start Time]@row, 4, 2)) / 60), 0)))
Time difference column:
 =VALUE(IF(FIND(".", Difference@row) > 0, MID(Difference@row, 1, FIND(".", Difference@row)  1), Difference@row)) + ":" + IF(FIND(".", Difference@row) = 0, "00", IF(AND(VALUE(Difference@row) < 0.16666, VALUE(Difference@row) > 0), "0" + ROUND(VALUE(IF(FIND(".", Difference@row) > 0, MID(Difference@row, FIND(".", Difference@row), 5), 0)) * 60, 0), ROUND(VALUE(IF(FIND(".", Difference@row) > 0, MID(Difference@row, FIND(".", Difference@row), 5), 0)) * 60, 0)))
If this answer answers your question, please press "Yes" above  it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick  Connect with me on LinkedIn.

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1yearold twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Brett  thanks so much for this (and the formulas!)  I was able to incorporate it in a sheet I have and it works great. Really appreciate this.
Kim

@Brett Wyrick this is incredible! Is there a way to flip this logic so that the end time is generated by the time difference? I'm looking to use my 'duration' column as the time difference column, so that it will automatically update the end time.

@Meredyth Norrman There should be something in here for you:

Hey @Brett Wyrick I am currently working on to calculate the SUMIFS time difference by date, and shift. However the result returns to zero.
Is it possible to sum it in the first place?

Hey @Brett Wyrick , I am currently trying to SUMIFS all the Time Difference by date and shift but it returns to zero. Is it possible to sum it it the first place?
Thanks

@Aneesa H  I'm going to be frank, I haven't touched Smartsheet in roughly two years! I started a new position at Microsoft and well, I've been off Smartsheet since then, basically. I just happened to login to the forums today and saw your comment above.
Interesting solution you've got there. Looks like "Shift" is deriving AM/PM from the "Created Date", and then "Time In" is deriving the created time from "Created Date". Is that right?
If I'm understanding you correctly, you want to create a "rollup" sort of formula that will show the total amount of time per day somehow? Is that accurate? Off the top of my head, I would think that you'd want to indent each row under particular days, if possible  that'd make it easier.
If this answer answers your question, please press "Yes" above  it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick  Connect with me on LinkedIn.

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1yearold twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Looks like this is now a feature request, which you can upvote here:
If this answer answers your question, please press "Yes" above  it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick  Connect with me on LinkedIn.

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1yearold twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

There is a TIME function that has been out for a couple of weeks now.
Help Article Resources
Categories
Check out the Formula Handbook template!