Time duration

Greg Zinn

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!

Brett Wyrick

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.

Greg Zinn's Time Troubles

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! 👁👁)

Greg Zinn

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.

Brett Wyrick

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)))

Kim Shaddix

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

Meredyth Norrman

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

Paul Newcome

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

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Aneesa H

Aneesa H

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?

Aneesa H

https://community.smartsheet.com/discussion/comment/299839#Comment_299839

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

Brett Wyrick

https://community.smartsheet.com/discussion/comment/387984#Comment_387984

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

Brett Wyrick

Looks like this is now a feature request, which you can upvote here:

Please let Smartsheet work with time — Smartsheet Community

Paul Newcome

There is a TIME function that has been out for a couple of weeks now.

https://help.smartsheet.com/function/time