Formulas for Calculating Time

L_123 · 08/18/20

https://community.smartsheet.com/discussion/70506/hh-mm-calculation-in-smartsheet

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 · 08/18/20

@L@123 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

L_123 · 08/18/20

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 · 08/18/20

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.

Anthony Barthelemy · 09/29/20

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

Paul Newcome · 09/30/20

@Ray B Exactly which one are you looking for?

Anthony Barthelemy · 09/30/20

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 · 09/30/20

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

Anthony Barthelemy · 09/30/20

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

Paul Newcome · 10/01/20

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

Anthony Barthelemy · 10/02/20

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

Andrée Starå · 10/02/20

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!

Paul Newcome · 10/05/20

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

Andrée Starå · 10/07/20

@Paul Newcome

Happy to help!

Addison Spencer · 11/23/20

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

Formulas for Calculating Time

Comments

Categories