Creating a Calendar

[GerryBriant]

My conundrum is..
On sheet 1, I have a 2 columns (A & B) containing multiple dates.
Column A' is 'Task start date' & Column 'B' is 'Task end date'.
On sheet 2, Row 1 contains incremental dates.
The outcome I am trying to work out is
Sheet 1 start date (A2) = 01/01/2018, end date (B2) = 03/01/18
Sheet 2 I would expect (or am hoping) to see the following against the column dates
A1 is 01/01/2018, & A2 = 1, B1 is 02/01/2018, & B2 = 1, C1 is 03/01/208, & C2 =1, D1 is 04/01/2018 & D2 = 0 (or blank), E1 is 05/01/2018 & E2 = 0 (or blank) etc.
PS date formats are UK
Any suggestions or guidance?

 

[FormR]

I have a 2 columns (A & B) containing multiple dates.

Hi, welcome to the forum!

It's not clear (to me at least) what happens when there are multiple dates, but based on the single row example that you laid out - here is one option.

Excel 2013/2016
	A	B	C	D	E	F
1	01/01/2018	02/01/2018	03/01/2018	04/01/2018	05/01/2018	06/01/2018
2	1	1	1	0	0	0
Sheet2

Cell Formulas
Range	Formula
A2	=0+AND(Sheet1!$A$2<=A$1,Sheet1!$B$2>=A$1)

Excel 2013/2016
	A	B
1	Task Start	Task End
2	01/01/2018	03/01/2018
Sheet1

 

[GerryBriant]

many thanks for the greeting & suggested help but maybe I wasn't to clear...
Sheet 1 contains a range of dates... '=0+AND(Sheet1!$A$2:$A$1000<=A$1,Sheet1!$B$2:$B$1000>=A$1)' doesn't seem to want to work.

Activity Start Date	Activity End Date
01/01/18	03/01/18
01/02/18	03/05/18
01/01/18	03/02/18

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[FormR]

Activity Start Date	Activity End Date
01/01/18	03/01/18
01/02/18	03/05/18
01/01/18	03/02/18

<tbody>
</tbody>

With a range of dates like so, what are your expected results?

 

[GerryBriant]

Thanks FormR,
I would expect to see on sheet 2 (the calendar range) to be populated with '1' till 03/05/18 & zero against 04/05/18 onwards.

 

[FormR]

Thanks FormR,
I would expect to see on sheet 2 (the calendar range) to be populated with '1' till 03/05/18 & zero against 04/05/18 onwards.

OK - you could try:

Excel 2013/2016
	A	B	C	D	E	F
1	01/01/2018	02/01/2018	03/01/2018	04/01/2018	05/01/2018	06/01/2018
2	1	1	1	1	1	1
Sheet2

Cell Formulas
Range	Formula
A2	=0+(COUNTIFS(Sheet1!$A:$A,"<="&A$1,Sheet1!$B:$B,">="&A$1)>0)

 

[GerryBriant]

Hey FormR,
That worked!!
Thank You
I am confused about the use of ""& in a formulae though!!

 

[FormR]

I am confused about the use of ""& in a formulae though!!

Hi, criteria part of the COUNTIFS() function is expecting a string - the ampersand character is used to concatenate the "<=" with the date that is in cell above to create that string.

 

[GerryBriant]

Thank You FormR,
I have incorporated your suggestion in a nested formulae (to select against a employee - =SUM(IF(Sheet1!$A:$A=$A6,(0+(COUNTIFS(Sheet1!$D:$D,"<="&H$3,Sheet1!$E:$E,">="&H$3)>0)))) but I am not getting the expected result...

Sheet1

Resource (Select from Dropdown)	Activity Start Date	Activity End Date
A. Gibbs	01/01/18	03/01/18

<tbody>
</tbody><colgroup><col><col><col></colgroup>

Sheet2

	BH	Tue	Wed	Thu	Fri	Sat	Sun	Mon
Employee	01	02	03	04	05	06	07	08
A. Bugg	3	3	3	3	3	3	3	0
A. Edwards	1	1	1	1	1	1	1	0
A. Gibbs	1	1	1	1	1	1	1	0

<tbody>
</tbody><colgroup><col><col><col span="7"></colgroup>

 

[GerryBriant]

Brilliant.
A clear n concise answer I understood!!
Thanks