Determining Status from a date range

[mattmickle]

Working with some data and I'm having a tough time working through a formula.

Two Individuals:
EMP Start End Program Start End Program
123 1/1/16 12/31/16 Program A 9/11/16 12/31/16 Program B
456 1/1/16 12/31/16 Program A 6/20/16 12/31/16 Program C

So each person was offered Program A on 1/1/16, but each started a different program later in the year.
I need to be able to break it up so that I can show:

EMP JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV
123 ProA ProA ProA ProA ProA ProA ProA ProA ProB ProB ProB
456 ProA ProA ProA ProA ProA ProC ProC ProC ProC ProC ProC


Does this make sense?
SO the formula needs to look at each date range, determine which months it's valid for, while also looking at a second, or third, or fourth date range to determine if a new program started.

 

[oldbrewer]

Working with some data and I'm having a tough time working through a formula.

Two Individuals:

EMP Start End Program Start End Program

123 1/1/16 12/31/16 Program A 9/11/16 12/31/16 Program B

456 1/1/16 12/31/16 Program A 6/20/16 12/31/16 Program C

So each person was offered Program A on 1/1/16, but each started a different program later in the year.

I need to be able to break it up so that I can show:

EMP JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV

123 ProA ProA ProA ProA ProA ProA ProA ProA ProB ProB ProB

456 ProA ProA ProA ProA ProA ProC ProC ProC ProC ProC ProC

Does this make sense?

SO the formula needs to look at each date range, determine which months it's valid for, while also looking at a second, or third, or fourth date range to determine if a new program started.

col N

col O

emp

start

end

program

start

end

program

123

01/01/2016

31/12/2016

A

11/09/2016

31/12/2016

B

01/01/2016

10/09/2016

11/09/2016

31/12/2016

A

A

B

B

row 26

456

01/01/2016

31/12/2016

A

20/06/2016

31/12/2016

C

01/01/2016

19/06/2016

20/06/2016

31/12/2016

A

A

C

C

emp

01/01/2016

01/02/2016

01/03/2016

01/04/2016

01/05/2016

01/06/2016

01/07/2016

01/08/2016

01/09/2016

01/10/2016

01/11/2016

01/12/2016

01/01/2017

row 32

123

A

A

A

A

A

A

A

A

A

B

B

B

B

formula giving A under 1/1/16 is

=IF(AND(B32>=$I26,B32<=$J26),$N26,$O26)

the helper columns are automatically calculated

<colgroup><col><col span="2"><col><col span="2"><col span="2"><col span="4"><col span="6"></colgroup><tbody>
</tbody>