Use Excel to calculate the hours worked for any shift

by Mary Ann Richardson | Jun 23, 2005 7:00:00 AM

Tags: Microsoft Excel, Microsoft Office, Mary Ann Richardson, Category List, Format Cells, Microsoft Office Suite Excel Tips Newsletter

Takeaway: With Excel, you can create a worksheet that figures the hours worked for any shift. Here's how.

To calculate in Excel how many hours someone has worked, you can often subtract the start time from the end time to get the difference. But if the work shift spans noon or midnight, simple subtraction won't cut it.

However, you can easily create a worksheet that correctly figures the hours worked for any shift. Follow these steps:

1. In A1, enter Time In.
2. In B1, enter Time Out.
3. In C1, enter Hours Worked.
4. Select A2 and B2, and press [Ctrl]1 to open the Format Cells dialog box.
5. On the Number tab, select Time from the Category list box, choose 1:30 PM from the Type list box, and click OK.
6. Right-click C2, and select Format Cells.
7. On the Number tab, select Time from the Category list box, choose 13:30 from the Type list box, and click OK.
8. In C2, enter the following formula:

=IF(B2<A2,B2+1,B2)-A2

If you enter 11:00 PM as the Time In and enter 7:00 AM as the Time Out, Excel will display 8, the correct number of hours worked.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Print/View all Posts Comments on this article

Calculating hours klarzuk@... | 06/29/05

If a lunch is taken... drayharris@... | 07/06/05

Person Worked from 830-5 Rita41ny@... | 03/19/08

This is a good idea, but you can also do this... drayharris@... | 07/06/05

Great help lm1210@... | 07/06/07

RE: Use Excel to calculate the hours worked for any shift matthew.m.hinkle@... | 01/17/08

RE: Use Excel to calculate the hours worked for any shift kaalia76@... | 05/14/08

You can download a ready Timesheet Template UAddUp | 09/10/09

RE: Use Excel to calculate the hours worked for any shift vinodjames | 12/07/09

Excel Template for overtime UAddUp | 12/28/09

RE: Use Excel to calculate the hours worked for any shift noelmonzon1616 | 02/06/10