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.....
I am looking for this formula too. ;)
My apologies. This thread got lost in the mix. One more question...
Will you need to account for date overlap such as
18 Feb 18:00 - 19 Feb 06:00
HERE is a published sheet with a possible solution.
The Difference column is the column for display with the result showing as HH:MM. The SUM column is the one you would use in formulas to add up your times across the week/month/etc.
Hi Paul, thanks for continuing to work on this for me. The link above does not appear to work. When I click on it nothing happens. Not sure if I'm doing something wrong. Thanks again, appreciate the help.
Try THIS ONE. I am not sure why the other one didn't work.
Still having the same issue, I see the a line at the bottom of my screen for a few seconds and then nothing...
Ok. Let's try it this way then...
Thank you, got it now! This is super helpful, thank you so very much, really appreciate the help on this one.😀
Happy to help! 👍️
Paul......This is great and helpful... How would you calculate workday hours and exclude holidays?