# Basic timesheet formula with breaks

At the core, this formula subtracts start time from end time to get duration in hours. This is done to calculate both work time and break time.

Next, break time is subtracted from work time to get "net work hours".

This formula uses the the MOD function to handle times that cross a day boundary (midnight). By using MOD with a divisor of 1, positive results are unchanged, but negative results (which occur when start time is greater than end time) are "flipped" to get a correct duration.

For more details, see: How to calculate number of hours between two times

### Formatting time durations

In cases where calculated time exceeds 24 hours, you may want to use a custom format like [h]:mm. The square bracket syntax [h] tells Excel to display hour durations of greater than 24 hours. If you don't use the brackets, Excel will simply "roll over" when the duration hits 24 hours (like a clock).

### Alternative timesheet layout

The screenshot below shows an alternative format to capture time worked. Instead of logging work and break time separately, this version captures two separate in/out times for a single shift.

For this layout, the formula used in F5 is:

Instead of subtracting break time from work time, we add together two work times.

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

## Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.