Count complete aligned minute blocks
Count the 15-minute blocks that fit wholly between 2:30:15 PM and 5:10:00 PM.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | C8*60 | The block size in seconds. LET calls it n. | 900 |
| 2 | CEILING.MATH( … ) | The start, moved up to the next block mark. LET calls it s. | 2:45:00 PM, in seconds |
| 3 | FLOOR.MATH( … ) | The end, moved back to the last block mark. LET calls it e. | 5:00:00 PM, in seconds |
| 4 | IF(B8<A8, … ,MAX( … )) | Flags an end before the start, or counts blocks from s to e. | 9 |
| 5 | LET( … ) | 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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- 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.
- 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
OTHER ROUTES YOU MIGHT LIKE

Build a signed duration from its parts
-, 49, 30 and 15 → -2.062674 days
Get the template →
Convert durations to days by unit label
90 Minutes → 0.062500 days
Get the template →
Build a time from hour, minute, second
14 + 30 + 15 → 2:30:15 PM
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →