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?
@Shelly Toy Happy to help. 👍️
Paul Newcome is amazing! He is the Smartsheet Time Lord. Thanks Paul
@Paul Newcome
My form is very simple and I may be over thinking it but I can't get your formula to work on my sheet.
Basically my employees will key in their start time (Always AM), their end time, and their lunch break time (in 30 min increments). I need the total hours calculated based on start time / end time with the lunch break subtracted from that. Example
@Angie M Graham Feel free to take a look through this thread. There are quite a few time based solutions spread throughout the pages that should allow you to piece a solution together.
I figured it out! Thanks @Paul Newcome
@Angie Graham Great! Happy to help. 👍️
Paul -
I'm trying to work with the formulas on your Date/Time Year, and I get #UNPARSEABLE error using the same data and copying the formula in.
I have the same problem with the Calculated Time Worked for Employees sheet. The "Difference" formula gives me the same "UNPARSEABLE" error, although the SUM formula works just as your sheet does.
The date columns are formatted as dates....do you have any insight as to why I can't recreate this solution?
Thanks!
@LadyHerbie I would need to see screenshots of the sheet and what your actual formulas are (the ones causing the errors).
Thanks for taking a look (Here's the screenshot - I can't seem to publish outside my Org).
Difference: =INT(SUM@row) + ":" + IF((SUM@row - INT(SUM@row)) * 60 < 10, "0") + (SUM@row - INT(SUM@row)) * 60
Sum: =((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End Date]@row - [Start Date]@row) * 24) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)
and the other one:
=Years@row + IF(Years@row = "", "", IF(Years@row = 1, " Year ", " Years ")) + Months@row + IF(Months@row = "", "", IF(Months@row = 1, " Month ", " Months ")) + Days@row + IF(Days@row = "", "", IF(Days@row = 1, " Day ", " Days ")) + Hours@row + IF(Hours@row = 1, " Hour ", IF(Hours@row = "", "", " Hours ")) + Minutes@row + IF(Minutes@row = 1, " Minute ", IF(Minutes@row = "", "", " Minutes "))
The data and formula are taken directly from your sample data.
@LadyHerbie Are you able to provide a screenshot of the formula causing the error actually in the sheet similar to the screenshot below?
I went back into the sheet this morning and all of a sudden the first of the 2 unparseable errors is gone and the spreadsheet is calculating fine. I don't understand how - haven't touched it since I sent you the last screenshot.
this one is working fine now:
this one still isn't
@LadyHerbie Do you have columns specifically titled "Years", "Months", "Days", "Hours", and "Minutes"?
ACK no. I truncated the end of the spreadsheet and was focused on the first several columns. Wow...didn't realize I did that...was so focused on the first several columns!
Thanks so much for your solutions and your time!
@LadyHerbie Happy to help. 👍️
I have tried using your formula you published here: https://app.smartsheet.com/b/publish?EQBCT=bde3993a5b7e408e9f112d2049f76662&_ga=2.95709276.1500217589.1687974941-243632862.1670021406&_gl=1*88g69a*_ga*MjQzNjMyODYyLjE2NzAwMjE0MDY.*_ga_ZYH7XNXMZK*MTY4Nzk3NDk0MS45LjEuMTY4Nzk3NDk4Ni4xNS4wLjA.
But I am getting #INVALID OPERATION in the difference column and in the Sum column.
Here is showing the formulas:
What have I missed?
Thank you for any assistance.
Date Report Partner CBO Name 01/12/25 Children's org 05/15/25 Foodbank 08/30/25 Children's org 09/11/25 Chamber 11/25/25 Digital Bus 08/21/24 Digital Bus South 01/15/26 Children's org 01/12/24 Foodbank Sister 02/15/24 For the children org 07/01/26 Digital Bus #2 08/05/26 Family Center 12/01/26 Family Center I am trying to…
I am trying to get the passenger count per month per year, and I can't seem to get the formula correct. I need to add the number of passengers for each month per year. If anyone could assist me with this, I would greatly appreciate it. Thanks!
I need to edit a large sheet that has a lot of predecessors. I need to delete one row and move another. when I make the changes the predecessors for proceeding lines are not correctly displaying the dates correctly. Need help.
