Time Tracking - Hours and Minutes

I was working on a way to track some task hours and minutes... came up with this:

One of the problems I ran into is that if you put "00" (double-ought) or any minute with a preceding zero, it would error. You can see the work-around I made, not the most elegant, I admit.. If Smartsheet could ignore preceding zeros, it would save a lot of IF statements (see [Start Time] and [End Time] columns)

https://app.smartsheet.com/b/publish?EQBCT=90d9dcad6ed246a89a1dbe6cfdce315d

Find more posts tagged with

Sales

Human Resources

Manufacturing

Templates

Non-profit

Construction

Sheets

Hospitality

Healthcare

Finance

Calendar View

Education

Formulas

Government

Reports

Real Estate

Attachments

Services

Legal

Marketing

Support

IT & Operations

Product Development

Project Management

Update Requests

Media & Entertainment

Enhancement Request

Comments

L_123

https://app.smartsheet.com/b/publish?EQBCT=9c639decdb034095adae7dc0e3e9915f

I managed to simplify it a little bit, and also added an end date so it can work for multiple days. Also I made it so you can type any number of zeroes before your minutes or hours and it will still work.

Ezra

Well done. Much more robust and elegant.

L_123

I don't know about that, but I've found that, in smartsheet at least, the more simple the solution is the best way to go. Feel free to take my solution. i've done this several times, and each time it seems to get easier. The one below is one of my favorites i've built, though it is outdated in what formulas I used

https://app.smartsheet.com/b/publish?EQBCT=dcf71ecb7f184467ab8f5debb68a04da

This one is viewed from the links i've posted in the past on the community almost daily, and I use it very often to scare our employees who want to try dealing with time in smartsheet.

Julie Barbeau

Hi @L_123 , Someone can explain me how I can use your template? It would be much appreciated. Thanks :)

L_123

There are 2 solutions on that template. The left of the black line, and the right. The left uses modified/created columns for the date/times, the right uses manual input for the date/times. If you want to use one or the other, you can hover your mouse over the formulas to review them.

That said, these are fairly old solutions, we've found better and different ways to calculate time.

@Paul Newcome has quite a few of them here that have better instructions and more specific use cases:

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

Sign in to join the conversation!

Quick Links

Trending discussions

Help with addition to formula
Hi, i need to make an addition to the below formula please. I need to add if column type is issue and impact score is 4 - gray, 3 - red, 2, amber and 1 green =IF(AND(Type@row = "Risk", ([Impact Score]@row * [Likelihood Score]@row ) >= 20), "Gray", IF(AND(Type@row = "Risk", ([Impact Score]@row * [Likelihood Score]@row ) >=…
index match using multiple critieria
Hello, I want to use a formula using INDEX/ MATCH Function using multiple criteria to get a single value I am trying to get a value of Column Description from File 2 based on matching Source System ,Table name and Attribute Field Index ( as the column names may be different ) The formula Im using gets me an INVALID…
How can I use Conditional Formatting for date in the past or future when a formula created the date
Hi Everyone, Thank you for your time in reading this. How can I use Conditional Formatting for date in the past or future when a formula created the date? The black columns are different dates generated. The 2 yellow columns are then selecting the relevant date based on different criteria. The white columns are me trying…