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?
Hi all. I'm trying to do something that I'm not sure if it's possible or not. We have a sheet that we are importing where the first column lists areas within our factory. Each column after that has a column name of a date. So 12/15/2025, 12/16/2025, 12/17/2025, etc. In each of the date columns is a target production…
Hello! I have what is probably a simple question my brain is just not comprehending properly. I need to SUMIFS from a sheet that matches some specific criteria but one of the fields I am using against needs to calculate a total if one dropdown field matches ANY ONE of these 3 items that is in it to be chosen… Capitol…
Hello, I have a checkbox column that I would like to be checked when comparing 2 columns. I would like it to be checked if the 2 columns are not equal or if one of the 2 columns has an error. It's important to note that both columns are numbers. My original formula was: =IF([COLUMN A]@row <> [COLUMN B]@row , 1, 0) However,…
