Hi - Dave here.

Happy Friday!

This week, I worked on a formula that has vexed me for a long time.

The goal is to look at a time span (like a work shift) and figure out how many hours overlap specific time blocks across that span.

You can see the basic idea in the worksheet below, where various start and end times are entered in columns B and C, time blocks are defined in rows 2 and 3, and the hours that fall into these blocks are calculated in the range F6:H17.



[Download the workbook and read the full explanation]

This doesn't seem like it would be that difficult, but the devil is in the details. The main challenge comes from handling work shifts and time blocks that cross midnight. Here is the core formula, which uses the LET function to define variables and reduce redundant operations:

=LET(
  start,$B6,
  end,$C6,
  blockStart,F$2,
  blockEnd,F$3,
  startDay,INT(start),
  endDay,INT(end),
  windowStart,startDay+blockStart,
  windowEnd,startDay+blockEnd+(blockEnd   startDayHours,MAX(0,MIN(end,windowEnd)-MAX(start,windowStart)),
  endDayHours,IF(endDay>startDay,
  MAX(0,MIN(end,endDay+blockEnd+(blockEnd   MAX(start,endDay+blockStart)),0),
  startDayHours+endDayHours
)

Yes, I know. So much code!

In some ways, I think the most interesting part of this example is using the LAMBDA function to hide all of that crazy code above inside a custom function that you can call like this:

=BlockHours($B6,$C6,F$2,F$3)

Using named LAMBDAs is a really nice way to package up complicated logic into a simple formula that is easy to use without macros or VBA. Click the link above for the full explanation and to download the worksheet and try it yourself.

Note: This example requires Excel 2024 or Excel 365.

Excel formulas

We maintain a list of over 1000 working formulas here.

If you need more structure, we also offer video training.

Have a great weekend!

Dave

The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at how to solve a specific problem in Excel. You can sign up on our home page.

Exceljet
P.O. Box 4804
Salt Lake City, UT 84110

Copyright © 2025 Exceljet, All rights reserved.
You received this email because you are subscribed to our newsletter.
To unsubscribe, click the link below.