Search code examples
excelexcel-formulatimerangeexcel-2007

Extract Excel items in time range to consecutive time

Here’s a simple table with items in a time range. The desired result table has all items mapped to their consecutive time slots. What formula would you suggest accomplishing this task? Thanks!

Original table:

Item Start End
BE 8:00 AM 8:02 AM
zeta 8:06 AM 8:24 AM
gamma 8:13 AM 9:02 AM
beta 8:49 AM 9:13 AM
BE 8:56 AM 8:58 AM

Result table:

Time BE zeta gamma beta
8:00 AM Y
8:01 AM Y
8:02 AM Y
8:03 AM
8:04 AM
8:05 AM
8:06 AM Y
8:07 AM Y
8:08 AM Y
8:09 AM Y
8:10 AM Y
8:11 AM Y
8:12 AM Y
8:13 AM Y Y
8:14 AM Y Y
8:15 AM Y Y
8:16 AM Y Y
8:17 AM Y Y
8:18 AM Y Y
8:19 AM Y Y
8:20 AM Y Y
8:21 AM Y Y
8:22 AM Y Y
8:23 AM Y Y
8:24 AM Y Y
8:25 AM Y
8:26 AM Y
8:27 AM Y
8:28 AM Y
8:29 AM Y
8:30 AM Y
8:31 AM Y
8:32 AM Y
8:33 AM Y
8:34 AM Y
8:35 AM Y
8:36 AM Y
8:37 AM Y
8:38 AM Y
8:39 AM Y
8:40 AM Y
8:41 AM Y
8:42 AM Y
8:43 AM Y
8:44 AM Y
8:45 AM Y
8:46 AM Y
8:47 AM Y
8:48 AM Y
8:49 AM Y Y
8:50 AM Y Y
8:51 AM Y Y
8:52 AM Y Y
8:53 AM Y Y
8:54 AM Y Y
8:55 AM Y Y
8:56 AM Y Y Y
8:57 AM Y Y Y
8:58 AM Y Y Y
8:59 AM Y Y
9:00 AM Y Y
9:01 AM Y Y
9:02 AM Y Y
9:03 AM Y
9:04 AM Y
9:05 AM Y
9:06 AM Y
9:07 AM Y
9:08 AM Y
9:09 AM Y
9:10 AM Y
9:11 AM Y
9:12 AM Y
9:13 AM Y
9:14 AM
9:15 AM

Edit:

I just noticed that SUMPRODUCT() worked for most, but Beta is missing the first minute in the result table (8:49 AM). File is here, https://u.pcloud.link/publink/show?code=XZDCONVZ6mQCoqWVEJB0W7AKyLTvNje9z3IX

Any ideas?

Solution

  • Try using XMATCH( )

    enter image description here

    • Formula used in cell F2

    =IF(ISNUMBER(XMATCH(1,($E2>=$B$2:$B$6)*($E2<=$C$2:$C$6)*(F$1=$A$2:$A$6))),"Y","")

    Formula needs to fill down and fill right, also please try to change the cell references and ranges accordingly as per your suit.

    You can also use a BOOLEAN LOGIC within a SUM( ) function.

    enter image description here

    • Formula used in cell F2

    =IF(SUM(($E2>=$B$2:$B$6)*($E2<=$C$2:$C$6)*(F$1=$A$2:$A$6)),"Y","")

    Since OP is using Excel 2007 hence replace the formula using SUM( ) with SUMPRODUCT( ) although the SUM( ) would work but it would need to hit CTRL+SHIFT+ENTER while exiting the edit mode, also since you are not using the updated version, you can use MATCH( ) function in place of XMATCH( ) but using SUMPRODUCT( ) will be faster than the rest.

    Here is a screenshot where it shows it is not missing the first time 8:49 AM for Beta

    enter image description here

    Edit 8/10/2023

    Updated formula:

    =IF(SUMPRODUCT((TIME(HOUR($E2),MINUTE($E2),)>=$B$2:$B$6)*
               (TIME(HOUR($E2),MINUTE($E2),)<=$C$2:$C$6)*
               (F$1=$A$2:$A$6)),"Y","")

    enter image description here

    Download the workbook from here: Workbook