How to put date range sequentially between two co-workers in Excel?-CodePudding

I am working on a report for a project where I need to assign some date ranges to the co-workers.

I need to achieve this -

NAME      DATE
Kevin     1/10/2022 - 1/28/2022
Robert    1/10/2022 - 1/28/2022
Kevin     1/31/2022 - 2/18/2022
Robert    1/31/2022 - 2/18/2022
Kevin     2/21/2022 - 3/11/2022
Robert    2/21/2022 - 3/11/2022

I created this formula -

TEXT(DATE(2022,1,10) (ROW(1:1)-1)*21, "mm/dd/yy")&"-"&TEXT(DATE(2022,1,10) (ROW(1:1)-1)*21 18, "mm/dd/yy")

But now I do not know how can I use this formula to assign date ranges between these two people. One way to achieve is by doing Kevin or Robert first then use the other.

Like this -

    NAME      DATE
    Kevin     1/10/2022 - 1/28/2022  
    Kevin     1/31/2022 - 2/18/2022
    Kevin     2/21/2022 - 3/11/2022
    Robert    1/10/2022 - 1/28/2022
    Robert    1/31/2022 - 2/18/2022
    Robert    2/21/2022 - 3/11/2022

But do not want this way.

Any help would be appreciated.

CodePudding user response：

Use COUNTIF with a dynamic range instead of ROW:

=TEXT(DATE(2022,1,10) (COUNTIF($A$2:A2,A2)-1)*21, "mm/dd/yyyy")&"-"&TEXT(DATE(2022,1,10) (COUNTIF($A$2:A2,A2)-1)*21 18, "mm/dd/yyyy")