Calculating Time Worked for Employees
Comments

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

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

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

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.

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

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

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

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

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

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

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.

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?
Help Article Resources
Categories
Check out the Formula Handbook template!