Calculating Time Worked for Employees
Comments

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

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

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!

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

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

Something like this mockup:
No leading zeros, 12 hour time, and am and pm.

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

And how would it be entered if there was leave?

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 this works perfectly! Thanks so much!! You are awesome!!!🤗

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

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?

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

@Paul Newcome That worked!!! Thanks :)
Help Article Resources
Categories
Check out the Formula Handbook template!