I am trying to generate three separate vertical lists of all dates that will be worked by three individuals so I can eventually highlight them on a calendar. I was able to get the first line to work using the following post:

How To List All Dates Between Two Dates In Excel?

but I need it to iterate through the data list and add to the end every time it finds a match.

I am trying to generate three separate vertical lists of all dates that will be worked by three individuals so I can eventually highlight them on a calendar. I was able to get the first line to work using the following post:

How To List All Dates Between Two Dates In Excel?

but I need it to iterate through the data list and add to the end every time it finds a match.

• excel
• excel-formula

• 1 Maybe you don't need this in between step to highlight your calendar. Regardless, please sow what your result is supposed to look like. You showed for row 1. Where and how do you expect the values (and name for row 2, 3 and 4? Also please post data as markdown table. We don't want to type all this to reproduce your problem. – P.b Commented Feb 6 at 20:34

2 Answers 2

Maybe this is helpfull. (Requires M365):

=LET(b,B2:B31,
     c,C2:C31,
     d,D2:D31,
     s,SEQUENCE(,1+MAX(d)-MIN(c),MIN(c)),
     L,LAMBDA(x,TOCOL(IFS((s>=c)*(s<=d),x),2)),
SORT(HSTACK(L(b),L(s))))

This stacks the corresponding name next to the date values listed and in between dates along with these dates, sorted by name.

This can be accomplished using Power Query, available in Windows Excel 2010+ and Microsoft 365 (Windows or Mac)

To use Power Query

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to understand the algorithm.

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Remove Finished" = Table.SelectRows(Source, each ([Start Date] <> "Finished")),
    #"Changed Type" = Table.TransformColumnTypes(#"Remove Finished",{
        {"Rotation #", Int64.Type}, {"Name", type text}, {"Start Date", type date}, {"End Date", type date}}),

//Group by Name
//Then create sorted distinct list of all the dates
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {
        {"Dates", each 
            List.Sort(
                List.Distinct(
                    List.Combine(
                        Table.AddColumn(_, "All Dates", each
                            List.Dates(
                                [Start Date],
                                Duration.Days([End Date]-[Start Date])+1, 
                                #duration(1,0,0,0)),
                         type date)[All Dates]))), type {date}
                    }}),

//Create a separate column for each Name
    #"Separate Columns" = Table.FromColumns(
       #"Grouped Rows"[Dates],
       fnTableTypes(#"Grouped Rows"[Name], List.Repeat({type date}, Table.RowCount(#"Grouped Rows"))))
       
in
    #"Separate Columns"

The above will initially return an Error. You must also create a new blank query for a custom function with code as below and rename it fnTableTypes

(colNames as list, colTypes as list)=>
   
let
    a = List.Zip({colNames,colTypes}),
    
    #"Table Types" = Value.Type(
        List.Accumulate(
            a,
            #table({},{}), //seed with empty table
            (return_table, list_item)=>Table.AddColumn(
                return_table,
                list_item{0},
                each null, //no data, just headers
                list_item{1}
            )
        )
    )

in 
    #"Table Types"

Source Data

Results