Calculating Time Worked for Employees

Paul Newcome

Try THIS ONE. I am not sure why the other one didn't work.

sandra_m

Still having the same issue, I see the a line at the bottom of my screen for a few seconds and then nothing...

Paul Newcome

Ok. Let's try it this way then...

https://app.smartsheet.com/b/publish?EQBCT=bde3993a5b7e408e9f112d2049f76662

sandra_m

Thank you, got it now! This is super helpful, thank you so very much, really appreciate the help on this one.😀

Paul Newcome

Happy to help! 👍️

Debbie J

Paul......This is great and helpful... How would you calculate workday hours and exclude holidays?

Christina Watson

Hello - I need to calculate total time worked including a 30 minute lunch deduct or if the no lunch box is checked then I would need it to calculate straight total hours worked. Has anyone done this type of calculation? Thank you.

Jennifer Lenander

@Paul Newcome - I've reviewed your various posts on calculating duration of time but I'm still struggling. I found your published sheet on this (https://app.smartsheet.com/b/publish?EQBCT=bde3993a5b7e408e9f112d2049f76662&_ga=2.4928571.949348505.1606141002-1535956201.1594388618) and have your formula (=INT(SUM@row) + ":" + IF((SUM@row - INT(SUM@row)) * 60 < 10, "0") + (SUM@row - INT(SUM@row)) * 60) but I'm not following how to use that in my scenario below. Can you provide any assistance?

Thanks in advance.

Paul Newcome

@Christina Watson Do you already have formulas in place to calculate the time and just need help incorporating the lunch break?

@Jennifer Lenander That particular formula is to convert the end result back into a format that looks like "hh:mm". You would need to use the entire solution to include the formula in the SM column.

Lisa Smith

This is super helpful!

How do you sum the time worked for the day?

I have work total, lunch total, and leave total for the day. The format is 8:30 (hh:mm). When I enter =sum(work total) - (lunch total) - (leave total) I get #INVALID OPERATION.

Thanks!!

Paul Newcome

@Lisa Smith You would need to convert each of the times into a number. There are various time based solutions provided HERE. Feel free to take a look. If you have any questions or need help modifying a solution to fit your needs, please feel free to let me know.

Lisa Smith

Thank you Paul! Before delving too further deep into this, would a viable solution be to export the data to Excel to calculate? I'm new to Smartsheet, (obviously), so I'm not sure I'd be able to then import the totals back into a Smartsheet dashboard...

Paul Newcome

@Lisa Smith That would be an option, but when importing back into Smartsheet it will create a brand new sheet and the numbers could be stored as text values.

Your conversions should be in line with some of the more "simple" (<-- only relatively as compared to other time based solutions haha) solutions, so if you could provide answers to a couple of questions we should be able to come up with a solution relatively quickly for you.

It looks like you are only converting durations, so there is no need to worry about date overlap, am vs pm, etc. Correct?

If your hour is only a single digit such as 1, 2, 3, etc., will it be entered as 1, 2, 3, etc., or will it be entered as 01, 02, 03, etc.?

1:00 vs 01:00

Same with zero hours. Would it be 0:30 or 00:30?

Lisa Smith

Yes, only durations, as I'm capturing the date in another column. My fields are time workday started, time workday ended, lunch start, lunch end, and calculating the durations for the day. I anticipate the entries not to have leading zeros.

Paul Newcome

Ok. So do we need to go all the way back to the times the workday started and ended and the lunch start and end times, or are each of those durations already calculated/manually entered?