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

Babs_at_Didlake

Hi Paul:

Thank you for all your input and posting. Appreciate the time it takes to respond to these inquiries.

I'm very new to Smartsheet and formulas. Saw your note about the TIME function but still not clear on the formula provided.

I need to record the Start Time and End Time for an activity for employees on a daily basis to determine hours and minutes worked on that specific activity. Each activity will have its own entry. Time will not need to be calculated from one day to the next.

Example: Activity: Prep Start Time: 9:12 End Time: 11:25

Activity: Index Start Time: 1:43 End Time: 3:17

Would like to know if using a 24-hour clock is required? It is not preferred, but workable.

I have a column for Start Time and a column for End Time and would like the next column to indicate hours and minutes. If two columns for hours and minutes are necessary that's OK too.

Please write your response as if speaking to a 1st grader! :-)

Again, appreciate your support and thank you in advance.

Barbara

Paul Newcome

@Babs_at_Didlake Unfortunately we still have to use special formulas for determining the difference between two times. The TIME function is only for adding hours/minutes to an existing time and getting the new time as an output.

24 hour time is not required for entry although converting it to 24 hour (even in hidden columns) makes further calculations much easier. You should be able to find what you need here:

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