I have a start time and end time in my smartsheet but need to calculate hours worked. Has anyone used a formula before to do this? if so can you pass along?
How soon do you need this? Time calculations are "coming soon", and the workaround without involves a series of formulas across helper columns. If you need it A.S.A.P. I would be happy to help with just a few questions.
How do you want the end result displayed?
Are you using 12 hour or 24 hour time?
What format are the times entered in?
Are there any breaks that need to be accounted for?
I don't see your formula for this question and I could use the answer. I want to track my hours worked. I created a sheet which has a column for day, date, In, Out, Total hours and task details. I tried the simple excel formula =Out1-In1 but that returns #INVALD OPERATION. I've tried to do this kind of thing before and just ended up getting no where. So I'm looking forward to hearing from you. Many thanks in advance....
@Sandra Michaelis I didn't provide the formulas to the original question because I need the additional questions answered first. They all play a role in the setup. Feel free to answer those, and I will be more than happy to help you work out a solution.
@Paul Newcome thanks for your quick response, hopefully this answers all your questions:
End result = Total hours worked in my 'Total' column, can be a fraction, e.g. 8.25 hrs
I'm using 24 hr, military time - e.g. 13:00 to 17:00 hrs
I'm not counting breaks
Ok. Just a little more detail...
Are you definitely using the colon?
Are you using "hrs"? if so how consistently?
Definitely using colon - have a drop down list for the times
Hours can be part of, e.g. 1.25 hrs = 75 mins or 1 1/4 hrs
hope that answers your question
Ok. It takes a little bit of setup, so I may not have time to get this solution to you today.
For clarification so that the most accurate solution can be provided (I'll start working on the overall build, but I will need the below clarified before finalization)...
The times entered will NOT have "hrs" and will have the colon, but you would like the calculated result to include "hrs" as part of a decimal format?
Times will not have hours or any text-it is numeric only and always has the colon. I don't mind what the result looks like, could be the same format as the hours column. I do want to be able to total the hours column. for example, I want to be able to report I worked 39:25 hrs this week
Ok. I will work on this (I know how to do it but have to replicate my lost notes UGH!!) and get back to you once I have something set up for you.
Hi Paul and Sandra
Sorry to jump in on this conversation, but i am interested to see if this was resolved as i am looking for exactly the same.
I keep hoping that Paul will come up with the solution but nothing so far.....