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

The formula

| Number | Part | What it does | For row 8 |
|---|---|---|---|
| 1 | B8 | The end time. | 9:05 AM |
| 2 | -A8 | Takes away the start time. | -5:25 |
| 3 | 1 | One whole day. | 1 |
| 4 | MOD( … ) | 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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- 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.
- 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
OTHER ROUTES YOU MIGHT LIKE

Count whole hours in a duration
49:30 → 49 whole hours
Get the template →
Calculate time shared by two intervals
2:30–5:30 PM and 4:30–7:30 PM → 1:00 shared
Get the template →
Convert decimal hours to a duration
49.5 hours → 49:30
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →