Formulas for Calculating Time

2456710

Comments

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 08/18/20



    Here is an interesting one. Working with calculating time between start time and end time with values formatted in [HH].MM. First conversion to minutes between, then formatting back to [HH].MM format. It's amazing how much easier using a decimal instead of a colon makes the parsing

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @[email protected] Thanks for the addition! Looks like we were both working on some time based solutions at the same time. Haha.


    I agree. Using the decimal makes it MUCH more straightforward. Even if the entries were stored as text values it would be as simple as a VALUE function tucked into a few spots.


    The only thing I would have done differently would be to have used an INT function instead of ROUNDDOWN, but that is strictly personal preference. I always forget about the ROUNDDOWN function plus INT is less typing. Haha

    thinkspi.com

  • L_123
    L_123 ✭✭✭✭✭✭

    Yeah, the formula wasn't to long, and i felt that the phrasing of rounddown makes more sense reading it than int for people potentially less familiar with smartsheet formulas. If the formula had been more complex/longer i would have gone with int.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I can see how ROUNDDOWN would be a little easier to understand for those that are less familiar. I hadn't thought about it that way. Haha.


    Either way... Decimals instead of colons make life MUCH easier. I may start incorporating that into future solutions where we first use a VALUE/SUBSTITUTE to replace the colon with a decimal before running any further calculations.

    thinkspi.com

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    @Paul Newcome Is it Possible you can share a Sheet with this Time Formula?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Ray B Exactly which one are you looking for?

    thinkspi.com

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    The One that Allows me to Enter my Start Time and End Time to get the Total Elapsed Time (Format HH:MM) between the Start and End.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Ray B There are a handful of examples published in the original post as well as an extra one or two scattered throughout the comments. The exact one you need is going to depend on your details.

    thinkspi.com

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    Ok, I just need a visual of what already available so I can clone and then tweak.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    All of the links I have provided throughout this thread have all formulas and notes spelled out. Take a look at them, and you should see test/example rows as well as rows that contain text that can be very easily copy/pasted into your own sheet(s).

    thinkspi.com

  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭
    edited 10/02/20

    I did not see any links in this thread. Is their another thread with the links?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Ray B

    Here are some that are on the top of the first page.


    Calculating Time Worked for Employees

    Can you calculate time in Smartsheet?

    Conversion of timezones (Solved Formula Included)

    Date/Time/Year

    Flagging a Date and Time Overlap

    How do I create time of day columns?

    Need to create a "shift" column from a time column


    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Ray B They are mostly on the first page of the thread.


    Thanks @Andrée Starå for moving the bulk of them here to page 2!

    thinkspi.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Paul Newcome

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • @Paul Newcome Hi Paul,

    So I've been reading over your answers and trying to understand them (Smartsheet and Excel, I think that part of my brain just did not develop - I'm amazed at the formulas created!). I have this set up currently:

    Would these formulas work for calculating how many hours are worked in a week based on cells looking like this for each day? Or would it have to be more like a start time column and an end time column? The times could probably be adjusted but we're trying to keep columns to a minimum so it doesn't get busy/super complicated (They go Saturday-Friday). I apologize if this has already been answers but I just don't understand a lot of the answers in the various threads. Thanks!