Count complete aligned minute blocks

Count the 15-minute blocks that fit wholly between 2:30:15 PM and 5:10:00 PM.

What you'll make

In Excel: 2:30:15 PM to 5:10:00 PM in 15-minute blocks becomes 9.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1C8*60The block size in seconds. LET calls it n.900
2CEILING.MATH( … )The start, moved up to the next block mark. LET calls it s.2:45:00 PM, in seconds
3FLOOR.MATH( … )The end, moved back to the last block mark. LET calls it e.5:00:00 PM, in seconds
4IF(B8<A8, … ,MAX( … ))Flags an end before the start, or counts blocks from s to e.9
5LET( … )Names each step so the formula reads in order.9

Blocks start at midnight. A part block at either end doesn't count.

=LET(n,C8*60,s,CEILING.MATH(ROUND(A8*86400,0),n),e,FLOOR.MATH(ROUND(B8*86400,0),n),IF(B8<A8,"Review reversed interval",MAX(0,(e-s)/n)))

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 and end dates with times into the green columns of the Template tab. Add a block size in minutes. The block counts fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use a block size that fits evenly into a day, like 15, 30 or 60 minutes.

Microsoft's help: CEILING.MATH function