Hours that overlap specific time blocks

In this example, the goal is to calculate exactly how much of a task, shift, or event falls inside one or more defined blocks of time. The formula accepts a start and end time for the overall task or shift, as well as a start and end time for the block of interest. In the worksheet shown, the Start times are entered in column B, and the End times are in column C. Column E contains the total hours between Start and End times, and formulas in columns F, G, and H calculate the overlap in hours with the 3 custom time blocks shown. The number of time blocks can be adjusted to suit the use case.

Use cases

Here are some ways a formula like this might be used:

1. Calculating premium pay for hours logged in non-standard time periods.
2. Monitoring power or resource usage across peak and off-peak hours.
3. Tracking hours logged for workers on call 24 hours a day.
4. Tracking downtime during business-critical periods of the day.

Note that the Start and End times are actually "datetimes" (date + time). This is an important detail because it makes it easy to determine if the times cross midnight. You are free to adjust the number formatting as you like.

Datetimes for Start and End

All values in columns C and B are entered as Excel datetimes (date + time). In the formula bar, they look like this:

5/12/2025  6:00:00 AM // start (B6)
5/12/2025  2:00:00 PM // end (C6)

You are free to use any date formatting you like to display the datetimes. In the worksheet shown, all dates in the range B6:C17 are formatted with the following custom number format:

dd-mmm-yy h:mm

Using datetimes is an important simplification in the design of this worksheet because it minimizes calculations to determine if the start and end span midnight. As an added benefit, it also means we can calculate the total hours in the shift in column E with a simple formula like this:

=C6-B6 // total hours between start and end

As long as the start and end are datetimes, and the end > start, this formula will work correctly.

For details on how Excel stores dates and times, see this page. For a detailed walkthrough of different ways to calculate the hours between two times, see this page.

LET version of the formula

Without the LAMBDA wrapper, the LET version of the formula looks like this:

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

Tip: Use the keyboard shortcut Control + U to expand the formula bar to see more than one line at a time in longer formulas like this.

Notice the mixed references: $B6 and $C6 are set up to lock the column, and F$2 and F$3 are set up to lock the row. This is done so that the formula can easily be copied from F6 down and across the full range of F6:H17. The screen below shows the formula in action:

Variable list

Here is a list of all the variables used in the above formula:

• start – the full Excel date‑time value at which the task, shift, or event begins (B6)
• end – the full Excel date‑time value at which the task, shift, or event ends (C6)
• blockStart – start time for the block of interest (F2)
• blockEnd – end time for the block of interest (F3)
• startDay – the calendar day (date) of start
• endDay – the calendar day (date) of end
• shift - 1-day shift if block crosses midnight and the start time < blockEnd
• windowStart – the blockStart converted to a window start datetime
• windowEnd – the blockEnd converted to a window end datetime
• startDayHours – hours that overlap the block on the start day
• endDayHours – hours that overlap the block on the end day

Note: the final result is hours as native Excel time. Multiply by 24 to convert to decimal hours.

Windows of time

Naming the many variables used in this formula in a meaningful way is tricky. The most difficult naming decision was windowStart and windowEnd, which are meant to express a "window in time". We already have the blockStart and blockEnd, but these are just times, not anchored to any particular date. When we calculate the overlap in hours that span midnight, we need to tie the time values to a date. For example, a time block might start at 10 PM and end at 3 AM. For a time shift that crosses midnight, we need to calculate the 2-hour overlap on the start date and the 3-hour overlap on the end date. To make calculating the overlap in times easier, windowStart and windowEnd are true datetimes.

LAMBDA version of the formula

The custom LAMBDA version of the formula uses the four main inputs as function arguments. The formula is named "BlockHours" and looks like this:

=LAMBDA(start,end,blockStart,blockEnd,
  LET(
    startDay,INT(start),
    endDay,INT(end), 
    shift,(blockEnd<blockStart)*(MOD(start,1)<blockEnd),
    windowStart,startDay-shift+blockStart,
    windowEnd,startDay-shift+blockEnd+(blockEnd<blockStart),
    startDayHours,MAX(0,MIN(end,windowEnd)-MAX(start,windowStart)),
    endDayHours,IF(endDay>startDay,
    MAX(0,MIN(end,endDay+blockEnd+(blockEnd<blockStart))-
    MAX(start,endDay+blockStart)),0),
    startDayHours+endDayHours
  )
)

Once named and defined with the Name Manager, you can call it like this:

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

Converting a formula to a named lambda involves wrapping the formula in the LAMBDA function and refactoring the formula to move the variable inputs into the LAMBDA as arguments. Then you define and name the formula with the Name Manager. For details, see our LAMBDA function page.

Generic formula for intersection

In the section below, we explain how the formula works, step by step. One of the things you'll notice in the code below is a pattern that repeats to calculate the overlap of two time intervals:

MAX(0,MIN(end,windowEnd) - MAX(start, windowStart))

This is a pattern you'll see in more advanced Excel formulas and in other languages. It's a clever way to measure the intersection of two one-dimensional intervals using MAX and MIN, which avoids more complicated nested IF statements. In plain English, it says:

1. Take the latter of the two start points.
2. Take the earlier of the two end points.
3. Subtract them to get the possible overlap.
4. If the result is negative (no overlap), force to zero (0).

How the formula works

The LET version of this formula handles time overlap calculations by breaking the problem into manageable pieces using named variables. Here's how it works:

Step 0: Get the inputs

To start off, the formula picks up four values directly from the worksheet:

  start,$B6,
  end,$C6,
  blockStart,F$2,
  blockEnd,F$3,

The start comes from cell B6, which contains the start time entered as a datetime. The end comes from cell C6, which contains the end time entered as a datetime. The blockStart variable is defined using the block start time in F2. The blockEnd variable is defined using the block start time in F3. 

Note, in the LAMBDA version of the formula, these four values are supplied directly as function arguments. 

Step 1: Extract the dates

startDay,INT(start),
endDay,INT(end),

These variables extract just the date portion from the start and end datetimes. This is important for handling shifts that cross midnight.

Note: The INT function is used to separate the date from the time.

Step 2: Create time windows

  shift,(blockEnd<blockStart)*(MOD(start,1)<blockEnd),
  windowStart,startDay-shift+blockStart,
  windowEnd,startDay-shift+blockEnd+(blockEnd<blockStart),

The formula converts the block times (like "8:00 AM" and "5:00 PM") into full datetimes by adding them to the start date. It also creates a shift variable that handles cases where (1) the block crosses midnight and (2) the start time is before the block end. If both conditions are TRUE, shift = 1, otherwise shift = 0. For overnight blocks where blockEnd<blockStart (like 11 PM to 6 AM), it adds 1 to push the end time into the next day. 

A good example of why this problem is challenging - the edge cases are tricky!

Step 3: Calculate overlap on the start day

startDayHours,MAX(0,MIN(end,windowEnd)-MAX(start,windowStart)),

This finds the overlap between the shift and the time block on the starting day. Note, we are working with the windows and not the block times, since the windows are anchored to dates. We use the intersection code explained above (MAX/MIN logic) to determine the actual overlap period, then subtract to get hours. The MAX(0,...) ensures that negative results are forced to zero (0).

Step 4: Calculate overlap on the end day (if different)

endDayHours,IF(endDay>startDay,
    MAX(0,MIN(end,endDay+blockEnd+(blockEnd<blockStart))-
    MAX(start,endDay+blockStart)),0),

For shifts spanning midnight, this code calculates overlapping hours on the end day. Again, we use the generic intersection formula explained above. The logic is similar to Step 3. Note that overlap is only calculated if endDay > startDay. Otherwise, IF returns zero (0).

Step 5: Sum the results

startDayHours+endDayHours

The final result adds the overlap hours from both days. The logic is somewhat complex, but this is because the formula is designed to handle complex scenarios like overnight shifts and overnight time blocks by treating each day separately, then combining the results.

Converting to decimal hours

The formulas above use Excel's native time units, in which time is expressed as fractional values of one day. However, you can easily convert the formulas to output decimal hours if needed. One option is to multiply the final result by 24 in the formula itself:

(startDayHours+endDayHours)*24 // convert to decimal hours

Another option with the LAMBDA version of the formula is to perform the same operation outside the formula:

=BlockHours($B6,$C6,F$2,F$3)*24 // convert to decimal hours

With either option, you will need to adjust the number formatting to display decimal hours as you like.

Multi-day version of the formula

One question that comes up when you're working on a formula like this is how to handle time shifts that span multiple days. One option is to use the SEQUENCE and MAP functions to call the custom LAMBDA once per day, like this:

=LET(
  start,$B6,
  end,$C6,
  blockStart,F$2,
  blockEnd,F$3,
  days,SEQUENCE(INT(end)-INT(start)+1,1,INT(start)),
  SUM(
    MAP(days,
    LAMBDA(day,
    BlockHours(MAX(start,day),MIN(end,day+1),blockStart,blockEnd))
    )
  )
)

Notice we are calling BlockHours inside the MAP function, which is looping over the days array created by SEQUENCE. The basic operation of this formula works in three steps as follows. First, SEQUENCE creates a date range as an array of calendar dates using the start and end datetimes like this:

SEQUENCE(INT(end)-INT(start)+1,1,INT(start))

Next, the MAP function applies the custom BlockHours function to each day.

MAP(days,
  LAMBDA(day,
  BlockHours(MAX(start,day),MIN(end,day+1),blockStart,blockEnd))

Note that for each day in the sequence, it calls BlockHours with a start time of MAX(start,day). This is because we want to use the actual start time on the first day, and midnight (day) on subsequent days. In a similar way, we use an end time of MIN(end,day+1) so that BlockHours will use midnight of the next day on intermediate days, and the actual end time on the final day. You can see the result in the worksheet below, where the start and end times have been adjusted to define larger time spans:

I think this is a cool example of how the new dynamic array functions in Excel can be used to extend the functionality of an existing formula in surprising and powerful ways. I haven't tested this thoroughly yet, but I think it works. Please let me know if you find otherwise! The multi-day formula is on the third sheet in the workbook.