Formula help required - 4 date columns

[RI_1978]

Require formula help. There are columns heading with monthly dates e.g Jan 20, Feb 2020 and so forth up to 2021. Value of 1 will be entered depending on dates.

Rows have details on resources together with 4 date columns for each resource. Example for Resource A
START DATE = 31/01/2020
END DATE = 31/12/2021
LEAVING DATE = 31/05/2020
NEW JOINER DATE = 31/08/2020

Criteria as follows required for formula

- Value of 1 will be entered in the monthly columns if START DATE = 01/01/2020 and END DATE = 31/12/2021 for resource A. Reflects resource working.
- If LEAVING DATE = 31/05/2020 and new JOINER DATE = 31/08/2020 then value of 0 should be entered from May 2020 up to Jul 2020. Aug 20 should have 1 as it reflects resource re-starting again.

Is there a formula in excel can execute the above or is VBA required?

 

[Yongle]

Try this and amend to suit your needs

Results

Date in G2, H2 etc is the LAST day of the month (which is calculated by formula in the example)
In the picture above those cells are custom formatted "mmm-yy"
That date is the used in the formula in row 3
=IF(AND(G$2>=$D3,G$2<$E3),0,IF(AND(G$2>=$B3,G$2<=$C3),1,0))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1							31/01/2020
2	Resource	Start	End	Leaving	New Join		31/01/2020	29/02/2020	31/03/2020	30/04/2020	31/05/2020	30/06/2020	31/07/2020	31/08/2020	30/09/2020
3	R1	31/01/2020	31/12/2021	31/05/2020	31/08/2020		1	1	1	1	0	0	0	1	1
Sheet4

Cell Formulas
Range	Formula
G2:O2	G2	=EDATE($G$1,COLUMN(G1)-COLUMN($G$1))
G3:O3	G3	=IF(AND(G$2>=$D3,G$2<$E3),0,IF(AND(G$2>=$B3,G$2<=$C3),1,0))

 

[RI_1978]

Perfect, it works. Super star