Calculating Time Worked for Employees

Paul Newcome

@Shelly Toy Happy to help. 👍️

Daniel Goodman

Paul Newcome is amazing! He is the Smartsheet Time Lord. Thanks Paul

Angie M Graham

@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

Paul Newcome

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

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Angie M Graham

I figured it out! Thanks @Paul Newcome

Paul Newcome

@Angie Graham Great! Happy to help. 👍️

LadyHerbie

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!

Paul Newcome

@LadyHerbie I would need to see screenshots of the sheet and what your actual formulas are (the ones causing the errors).

LadyHerbie

@Paul Newcome

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.

Thanks!

Paul Newcome

@LadyHerbie Are you able to provide a screenshot of the formula causing the error actually in the sheet similar to the screenshot below?

LadyHerbie

@Paul Newcome

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

Paul Newcome

@LadyHerbie Do you have columns specifically titled "Years", "Months", "Days", "Hours", and "Minutes"?

LadyHerbie

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!

Paul Newcome

@LadyHerbie Happy to help. 👍️

MSGFreebird

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.