Which formulae to use

[Jay3]

Hi Guys,

I have an export which gives me holiday start and end dates e.g. 01/08/2018 - 20/08/2018.

I need a formula which will enable me to look at all the dates in the range and enter the value 1 in each corresponding date column on my holiday chart.

Can anyone recommend a good formulae for this?

My work sheet as each month on a different tab. The dates go from left to right and the peoples names are in a column down the left.

Any advice appreciated.

Thanks,
John

 

[AlanY]

try this

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Start	End	01/08/2018	02/08/2018	03/08/2018	04/08/2018	05/08/2018	06/08/2018	07/08/2018	08/08/2018	09/08/2018	10/08/2018	11/08/2018
2	A.N. Other	01/08/2018	08/08/2018	1	1	1			1	1	1
3	A.N. Other1	02/08/2018	09/08/2018		1	1			1	1	1	1
Sheet1

Cell Formulas
Range	Formula
D2	=IF(AND(D$1>=$B2,D$1<=$C2,WEEKDAY(D$1,2)<6),1,"")

 

[Jay3]

Hi,

Thanks, I think that's definitely a step in the right direction.

So I have added that formulae to my download tab. Now I need to summarise the data by month and person and populate the main template.

So, for example....

Person (A) might have 3 lots of holiday booked in August but my summary would have:

Person (A) on the left column followed by the August dates across the top. So this would mean there would be 1 row for all of the august dates as opposed to several rows.

Is this even possible?

Thanks,
John