A client of mine asked me to setup a worksheet for them that calculates the number of hours they worked in a day based upon the time they arrived at work and the time they left. They also wanted it to deduct the number of hours they took for lunchbreaks.
Here’s the simple spreadsheet I setup for them using Microsoft Excel:
Columns A and B were used to fill in the days and dates. Columns C and D were used to enter the times they came and went each day. Column E indicates time taken for lunch. Finally, column F performs the calculations.
In cell F3, the end of the row for Monday, the following formula was used:
That formula was then copied and pasted into the rows below.
It may seem a little complex, but it’s a very versatile formula. Since they occasionally work a night shift, they sometimes leave work after midnight. Leaving after midnight means they left in the AM, and the formula, C3>D3, will be able to detect if they came in during the PM hours and left in the AM hours.
A regular 9 AM to 5 PM workshift is calculated as =24*(D3-C3)-E3. The portion in parentheses calculates the difference between the time they arrived and the time they left. The 24* converts it to hours out of 24 hour day, and the -E3 deducts their lunchbreak.
If they work a nightshift the Excel uses the formula =24*(D3+1-C3)-E3. The portion D3+1 indicates that they left work in the AM of the following day.
Post Comments or Questions with the link below. Keep up-to-date with Skylarking: By Email or RSS Newsfeed or on Twitter. You can also send questions with my email form. I’m looking forward to hearing from you.