Formulas for Calculating Time
Comments
-
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
-
@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
-
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.
-
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.
-
@Paul Newcome Is it Possible you can share a Sheet with this Time Formula?
-
@Ray B Exactly which one are you looking for?
-
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.
-
@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.
-
Ok, I just need a visual of what already available so I can clone and then tweak.
-
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).
-
I did not see any links in this thread. Is their another thread with the links?
-
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)
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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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!
-
Happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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!
