Formulas for Calculating Time

Paul Newcome

@Royce Salisbury I would suggest adding in a separate column for the time then just enter the hour. In your example just enter 3. Then you would need to set up potentially 24 separate automations to look have one of the conditions being that column. You would set up a separate automation to run triggering each hour and have the condition that the number in that column also matches the hour of the trigger as well as the date condition.

Sander K

Great thread @Paul Newcome.

I have a similar thing where I need to track employees working time. But I haven't yet figured out the end solution. And I also need to make the inputs by workers as easy and quick as possible.

How it should work: worker opens the form, chooses his activity and submits the form. There are ONLY 4 options to choose from vertical radio buttons: 1) Start Working 2) Start Coffee Break 3) Start Lunch Break 4) Stop Working.

Start Coffee and Lunch break should be equal to Stop working.

When form is submitted, I can see by whom it was submitted by with the "Created By" automation column. Same with the time, timestamp in the "Created" columns shows when it was submitted.

(Yes I am in Europe and use 24 hour work time 😀)

Now in theory I should be able to track in another sheet, where I have list of workers, and view how many hours they worked during the day, how long was their lunch break, coffee break etc. Maybe something like Primary column is worker name, then in different columns I have different periods of time like start working until coffee break, next one after coffee break until lunch time etc.. And in the end There is a summary column of Working time and summary column of non working time during the day.

Do you have any ideas or tips on how to exactly make it happen, because I know smartsheet is not very friendly regarding time management, minutes etc.

Thank you

Sander

Paul Newcome

@Sander K Do you need to accommodate working into another date such as clocking in at 2300 and clocking out at 0730 kind of thing, or will it be that everyone clocks out on the same date as when they clocked in?

Sander K

@Paul Newcome Mostly no, but in some cases yes. Also I thought maybe even putting only 2 options in the form: Time IN and Time OUT, and then only count the hours and minutes between time in and time out.

Sander K

@Paul Newcome I managed to get some things going based on your "Calculated Time Worked for Employees" sheet.

However I came across a fairly dumb issue. I used the same formulas you have in your sheet. If the value in "SUM" column is for example 2.16667, then it displays 2:010 in "Difference" column. It seems to happen when there are more digits after the decimal point.

And Total Working time of 2:010 is not really presentable as time in a report (answer should be 2:10). I tried to fix it with ROUND function to round to round it up or down, but that started to change the minutes enough so the answer becomes incorrect.

Do you have any ideas on how to correct that?

Paul Newcome

@Sander K Where exactly did you try to apply the ROUND function and how?

Sander K

@Paul Newcome I tried different combinations, don't remember where exactly. But it is now working well, just hoping I don't get the SUM value as 2.16667 😀.

How my setup works:

1. I made a form with 8 different inputs in a cell from dropdown list like Start Day, Start Break 1, End Break 1, End day etc.
2. I take all the names and times from autogenerated system columns (mandatory log in form)
3. Use pivot app to extract data from the form input file
4. In the pivot output file I set up lots of additional columns
5. Date I extract from the pivot date output, second date is just Date+1 IF Shift is night
6. All the time values, where you see the small blue triangles, are INDEX(COLLECT) combinations based on name, date and shift to take the data from form input file
7. Rest of the columns from "Break 1 Decimal" to "Total Day Time" are the same formulas you have in your "Calculated Time Worked for Employees" file (Decimal is Sum column, Time is difference column)
8. "Working time Decimal" is just "Total Decimal" minus all the break times. (Total Decimal - time between START and END day)

Now because this is not a perfect world where we live and people tend to make mistakes with form inputs or log out from breaks etc., I made some columns in the form input file where Date, Time or Name can be edited.

It would have taken me a really long time if it weren't for your "Calculated Time Worked for Employees" file, so I thank you @Paul Newcome for that.

Paul Newcome

@Sander K Glad my posts could help. 👍️

Savanna K

@Paul Newcome - I see you're extremely savy when it comes to calculating time.

I have a sheet with an auto Created date & time column and I want to calculate the hours from the created time to the current "now" time. Is that possible with a formula? Basically trying to track aged hours from the time stamp to current time.

Thank You!

Paul Newcome

@Savanna Kroll We can track it to within the hour. Anymore accurate than that is not possible within Smartsheet (unless you have some 3rd party apps at your disposal).

I will see if I can find another thread where I showed how to pull the date/time on an hourly updating basis and post a link here for you (once I am able to find it). Once you are able to pull the hourly updating date/time stamp, you should be able to use some of the various links throughout this post to run the actual calculations.

Savanna K

@Paul Newcome great, thank you!

Kate Kelley

Hi,

I used the formula in the original post by @Paul Newcome (Thanks!) but was wondering if I could have the time include the leading zero. I currently have it work so that it goes from 9:00 AM to 9:00. It would be great if it could read 09:00. Even trying 09:00 AM drops the leading zero after the formula. Any help is much appreciated.

Paul Newcome

@Kate Kelley Exactly which formula(s) are you using to generate the 9:00 that you want to add the leading zero to?

Michelle Basson

@Paul Newcome

I have been going through al your formula suggestions, and somehow I still get the incorrect information.

I am also looking at getting the time work for example: 07:00 till 17:00 would be 10 hours. when I put in your formula =((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)

I get 7. Not sure what I am doing wrong?

I need a calculation that does not take lunch into account as I would like to deduct the lunch hour on a separate column.

Any assistance would be appreciated.

Kindest regards

MBasson

Paul Newcome

@Michelle B Try something like this...

=(VALUE(LEFT([End Time]@row, 2) + (VALUE(RIGHT([End Time]@row, 2)) / 60)) - (VALUE(LEFT([Start Time]@row, 2) + (VALUE(RIGHT([Start Time]@row, 2)) / 60))

Because you are using the leading zero, we can cut out all of those FIND functions to simplify a bit. The above is just for if the hours are within the same date though. If you needed to account for date overlap, it would look more like this...

=((VALUE(LEFT([End Time]@row, 2) + (VALUE(RIGHT([End Time]@row, 2)) / 60)) + ([End Date]@row - [Start Date]@row) * 24)) - (VALUE(LEFT([Start Time]@row, 2) + (VALUE(RIGHT([Start Time]@row, 2)) / 60))