Calculating Time Worked for Employees

JoAnn Merritt

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?

Paul Newcome

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?

Sandra Michaelis

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....

Paul Newcome

@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.

Sandra Michaelis

@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

Paul Newcome

Ok. Just a little more detail...

Are you definitely using the colon?

Are you using "hrs"? if so how consistently?

Sandra Michaelis

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

Paul Newcome

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?

Sandra Michaelis

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

Paul Newcome

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.

dave.mcpherson56751

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

sandra_m

I keep hoping that Paul will come up with the solution but nothing so far.....

Vivien Chong

I am looking for this formula too. ;)

Paul Newcome

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

?

Paul Newcome

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.

sandra_m

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.