Calculate clock time across midnight

Turn a start of 11:30 PM and an end of 7:15 AM into 7:45, even past midnight.

What you'll make

In Excel: 11:30 PM to 7:15 AM becomes 7:45.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesFor row 8
1B8The end time.9:05 AM
2-A8Takes away the start time.-5:25
31One whole day.1
4MOD( … )Adds a day when the end is past midnight.18:35

Excel stores a day as 1. A negative gap means the end is the next day, so MOD adds one day.

=MOD(B8-A8,1)

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own times. Paste start times and end times into the green columns of the Template tab. The time between fills in by itself.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

This works for gaps under 24 hours. For longer ones, use full dates with times. The same time twice gives 0:00.

Microsoft's help: MOD function