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 an Excel worksheet that correctly figures the hours worked for any shift.

LEARN MORE: Office 365 Consumer pricing and features

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.

A bonus Microsoft Excel tip

From the article 10 things you should never do in Excel by Susan Harkins:

Rely on multiple links: Links between two workbooks are common and useful. But multiple links where values in workbook1 depend on values in workbook2, which links to workbook3, and so on, are hard to manage and unstable. Users forget to close files, and sometimes they even move them. If you’re the only person working with those linked workbooks, you might not run into trouble, but if other users are reviewing and modifying them, you’re asking for trouble. If you truly need that much linking, you might consider a new design.

This bonus Excel tip is also available in the free PDF 30 things you should never do in Microsoft Office.

Editor’s note on March 11, 2019: This Excel article was first published in June 2005. Since then, we have included a video tutorial, added a bonus tip, and updated the related resources.