Howdy. I am trying to calculate the number of hours an employee works using the start and end time, plus ten minutes, minus a half an hour for break.
So, for example, I have an employee who is scheduled from 2:00 - 9:30 who is required to clock in at 1:50 and has a half an hour clocked out for dinner break. How can I set this up in Excel so that I can simply enter in the start and end time and have Excel give me a result of total number of hours worked (with the result being number of hours and portion of hours used to calculate payroll. That is to say, so that seven and a half hours being 7.5 hours, seven hours and forty minutes being 7.7).
Just subtract the start and end time to get the hours/minutes. This formula will turn that into a decimal. You can format to display the result as 7.7 or round to actually make it 7.7
You have my sympathy. I worked for an agency at one time. I was paid by the hour and my start and finish times were highly variable. I wanted to be able to just enter start and finish times against the date in a form and I did manage to make it all work until I worked some night shifts. I gave up on trying to make it work when the start time was on a different day to the finish time.
Using sailor’s approach will get around the issue of starting and finishing on a different day. If you use the HOURS and MINUTES functions, you’ll need additional logic in case the time could span two days.