Count the time blocks a duration needs

A 36:10 duration needs 145 blocks of 15 minutes. Any part of a block counts as a whole one.

What you'll make

In Excel: 36:10 in 15-minute blocks becomes 145 blocks.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The duration, like 49:30.49:30
2*1440/B8Turns it into minutes, then counts 15-minute blocks.198
3ROUNDUP( … ,0)Rounds any part block up to a whole one.198

A day has 1,440 minutes. 36:10 is 2,170 minutes, or 144.67 blocks of 15, so it needs 145.

=ROUNDUP(A8*1440/B8,0)

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 durations. Paste durations and a block length in minutes into the green columns of the Template tab. The blocks fill in by themselves.
  3. 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 15, not as a time like 0:15.

Microsoft's help: ROUNDUP function