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?
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...
Ok. Let's try it this way then...
https://app.smartsheet.com/b/publish?EQBCT=bde3993a5b7e408e9f112d2049f76662
Thank you, got it now! This is super helpful, thank you so very much, really appreciate the help on this one.😀
Happy to help! 👍️
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.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.
@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?
I've found Paul Newcome's mega post on calculating hours and from that have successfully created the total number of hours worked. But I am trying to have it calculate net hours worked after taking away any unpaid break. Breaks will be 30 mins or 60 mins. I've seen other posts from people asking about it but haven't seen…
I have an automated workflow set up to run weekly and request an update when the project is in certain statuses. The automation only ran once and now it's not working even though I have it set up to run weekly. I need help troubleshooting why this isn't working.
Hello! I am attempting to create a dashboard widget that shows me how much budget we are spending in each segment, and in what category. The source sheet contains these columns… "Budget" - text/number column "Category" - dropdown list (restricted to list values only) "Segment" - dropdown list (allows multiple values per…
