Find minutes left after whole blocks
49:30 leaves 30 minutes after whole 60-minute blocks. Times round to whole minutes.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | The duration, like 49:30. | 49:30 |
| 2 | *1440 | Turns the duration into minutes. | 2970 |
| 3 | ROUND( … ,0) | Rounds to whole minutes. | 2970 |
| 4 | B8 | The block length in minutes, like 60. | 60 |
| 5 | MOD( … ) | Takes away whole blocks and keeps what is left. | 30 |
It works from the inside out. A day has 1,440 minutes. 49:30 is 2,970 minutes. 49 whole blocks of 60 use 2,940, so 30 are left.
=MOD(ROUND(A8*1440,0),B8)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 durations. Paste durations and a block length in minutes into the green columns of the Template tab. The minutes left fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Type the block length as plain minutes, like 30, not as a time like 0:30.
Microsoft's help: MOD function
OTHER ROUTES YOU MIGHT LIKE

Count the time blocks a duration needs
36:10 in 15-minute blocks → 145 blocks
Get the template →
Read 49.30 as 49 hours 30 minutes
49.30 (hours.minutes) → 49:30
Get the template →
Read negative elapsed time like -0:30
-0:30 → a negative 30-minute numeric duration
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →