Calculating Total Working Hours between Sign In & Sign Out

Kamil Ismail

Hello,

I'm facing difficulty of summing up Total Working Hours in the worksheet. I'm working on calculating a simple total working hours for my employee, they just fill in Smartsheet form then the worksheet will calculate it.

I need help on which solution work best on this project. Below I provide the details:

Employee will fill in timesheet form above with a free textbox for time in and time out.

So far I didn't find a proper solution on this

Please let me know what you think the optimal solution is to this problem, I am open to anything!

Paul Newcome

I have a handful of time based solutions spread out through the Community. We should be able to find you something...

I see you are using 24 hour time. Will this be standard?

In cases such as your above screenshot, you overlap midnight. Do you include dates, or is it assumed that it will never be more than 24 hours so that an end time smaller than a start time is simply considered the next day and not 2+ days?

Kamil Ismail

Yes, it will be standard to both column, but in the form user just type in the box without ":".

The screenshot is the initial testing with the formula then, I encounter problem when calculating both time & date. It is important as we will tracking employee working shift time and to validate date & time.

Paul Newcome

Ok. So what is the exact format the times will be entered in?

Will there be date overlaps where a start time is on one date and the finish time is on another date? If so, how are the dates recorded?

Kamil Ismail

User will entered 24H format: 0015, 1425, 2058.

I have put new column for system created date & time. I planned to capture from here. But then I don't know how to capture sign off time(Time out column).

Currently I stuck on this calculation and I don't know how to continue.

Paul Newcome

I'm not sure I follow... Are users manually entering start and end time and date, or are you capturing through other means?

If you let me know exactly how start and end times and dates are being recorded into the sheet, we can work from there. There isn't much that can't be done. I just need to know which layout and format you are using.

Kamil Ismail

Alright I describe the scenario together with the link.

1. Initially we have 2 forms to sign in & sign out that feed into one worksheet.
2. User typed in manually the time and day on both form with the same format. New Question, is it possible to use 2 forms to update the same row? I want to calculate based on this two column.
3. Using Created Date column(system generated), we could see the sign in time, for sign off I use modified Date column(system generated). This is just for double check.

Paul Newcome

To have a form type of layout to update a previously entered row, you would need to use an Update Request.

System generated columns can be used for the calculations, but that would require a different set of formulas.

Kamil Ismail

I don't use Update Request is because user need to fill in the form. Is it possible using two different worksheet combined, and calculate the time?

Could you share how you come up with the formula, please.

Paul Newcome

It is possible with 2 sheets.

I cannot share formulas until the exact setup and use is established. Any variation in how data is input can drastically change the appropriate formulas.

Let me know what method you decide on for entering the data, and then we can go from there.

Kamil Ismail

After discussion, we let user input the time in the freetextbox using 24 hours format without ":", example 2230, 1415.

Sorry for the delay.

Paul Newcome

Ok. So you have times recorded in 24 hour format without colons.

Is there the possibility that the start and end DATES could be different? If so, is it just going into the next day, or is there the possibility of multiple days between the start and end dates?

Kamil Ismail

Yes use 24 hours without columns.

No, we capture their time on the same day. They will be working in between 8AM till 5PM (working hours), they maybe work overtime 2 hours but not till the next day.

Paul Newcome

Try using this to calculate how many hours were worked on each row...

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