# Calculating Time Worked for Employees

Options
edited 12/09/19

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?

Tags:

• ✭✭✭✭✭✭
Options

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?

• Options

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

• ✭✭✭✭✭✭
Options

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

• edited 01/08/20
Options

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

• ✭✭✭✭✭✭
Options

Ok. Just a little more detail...

Are you definitely using the colon?

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

• Options

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

• ✭✭✭✭✭✭
Options

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?

• Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

I am looking for this formula too. ;)

• ✭✭✭✭✭✭
Options

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

?

• ✭✭✭✭✭✭
Options

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.

• ✭✭
Options

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!