Calculating Time Worked for Employees

Lisa Smith

They are not calculated yet.

The user would enter their start and end times for their workday, lunch period, and leave period. Then I'd like to be able to calculate the duration of the time worked each day.

😊

Paul Newcome

@Lisa Smith Have you browsed the solutions in the other link I provided earlier? THere should be one in there somewhere that calculates the difference between two times. You could use that to calculate the difference between the work start and end times then duplicate for the difference between the lunch start and end times. Then you could subtract those two numbers from each other to get the duration in an integer format. Then there is another solution out there to convert that integer into hh:mm format.

If you need any help piecing things together let me know. In the meantime I will work on putting something more specific to your needs together, but that may take a little while as my workload is rather cumbersome at the moment.

Lisa Smith

You are so kind to help me with this. I found the answer that calculates the date and time, but not just the time. I will continue to look and if I can't find it I'll use the existing formula. Thank you so much!

Paul Newcome

@Lisa Smith I thought certain I had something out there. Let me make sure I have everything correct and all in one place...

Are you using 12 or 24 hour times?

Start and end will always be on the same day meaning there are not going to be any times where you start at 11pm today and end at 7am tomorrow?

Your hours could be single digits (no leading zero)?

You have a workday start and end time and a lunch start and end time (with the lunch duration being subtracted from the workday duration)?

Where does the Leave Time you previously mentioned come into play? Would that work the same way as the lunches where you have a set start and end time, or is that a number such as "1" hour or "4" hours?

I may have other questions as I build it out in which case I'll update the thread here.

Paul Newcome

@Lisa Smith I will also need to know how you are designating "am" vs "pm" if you are using a 12 hour time.

Lisa Smith

Something like this mock-up:

No leading zeros, 12 hour time, and am and pm.

Paul Newcome

Does the leave work like the lunch where it subtracts from the workday total?

Paul Newcome

And how would it be entered if there was leave?

Lisa Smith

Yes, the leave would be entered the same... time out and time in. It would be subtracted from the total time worked for the day.

Paul Newcome

@Lisa Smith HERE you go...

Lisa Smith

@Paul Newcome this works perfectly! Thanks so much!! You are awesome!!!🤗

Paul Newcome

@Lisa Smith Happy to help. 👍️

I also put the solution in the thread that contains most of my time based solutions so that everything is all together. It is the thread I provided a link to earlier.

Shelly Toy

https://community.smartsheet.com/discussion/comment/240390#Comment_240390

Hi Paul, this solution worked for me with the exception of the following...

Start Time = 13:10

End Time = 20:15

Difference = 7:5

How do I change the formula to reflect the difference of 7:05?

Paul Newcome

@Shelly Toy

Exactly what do you have in the Difference column? Can you copy/paste directly from the sheet? Below is what is in the published solution. The bold portion is what should be adding the leading zero for minutes less than 10.

=INT(SUM@row) + ":" + IF((SUM@row - INT(SUM@row)) * 60 < 10, "0") + (SUM@row - INT(SUM@row)) * 60

Shelly Toy

@Paul Newcome That worked!!! Thanks :)