Sign in to join the conversation:
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?
Hi Paul,
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
8:00
8:15
8:30
8:45
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.
Regards
Dave
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
Start.......................End
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.
Hi all. I'm trying to do something that I'm not sure if it's possible or not. We have a sheet that we are importing where the first column lists areas within our factory. Each column after that has a column name of a date. So 12/15/2025, 12/16/2025, 12/17/2025, etc. In each of the date columns is a target production…
Hello! I have what is probably a simple question my brain is just not comprehending properly. I need to SUMIFS from a sheet that matches some specific criteria but one of the fields I am using against needs to calculate a total if one dropdown field matches ANY ONE of these 3 items that is in it to be chosen… Capitol…
Hello, I have a checkbox column that I would like to be checked when comparing 2 columns. I would like it to be checked if the 2 columns are not equal or if one of the 2 columns has an error. It's important to note that both columns are numbers. My original formula was: =IF([COLUMN A]@row <> [COLUMN B]@row , 1, 0) However,…
