Counting The Number of People on Leave on a Given Date

[JosephGiri]

Hi everyone,

I have a leave application data entry table that looks like this,
SheetName: Data

A: Name
B: Start Date
C: End Date​

I have a separate sheet that has the following structure,
SheetName: Counter

A: Date (from 01 Jan to 31 Dec)
B: No of People on Leave​

I need to create a non-VBA solution to populate column B in Counter.

A sample set of data is shown below,
SheetName:Data

	A	B	C
1	Name	Start Date	Return Date
2	P1	1-Jan	6-Jan
3	P2	1-Jan	7-Jan
4	P3	1-Jan	12-Jan
5	P4	4-Jan	5-Jan
6	P5	6-Jan	10-Jan
7	P6	10-Jan	12-Jan
8	P7	13-Jan	18-Jan
9	P8	19-Jan	21-Jan
10	P9	26-Jan	9-Feb
11	P10	28-Jan	1-Feb
12	P11	29-Jan	2-Feb
13	P12	30-Jan	6-Feb
14	P13	30-Jan	1-Feb
15	P14	30-Jan	3-Feb
16	P15	30-Jan	1-Feb
17	P16	31-Jan	5-Feb
18	P17	28-Feb	9-Mar
19	P18	18-Apr	23-Apr

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

My expected result (only showing for Jan) is,
ShhetName: Counter

	A	B
1	Date	Number of Ppl on Leave
2	01 Jan	3
3	02 Jan	3
4	03 Jan	3
5	04 Jan	4
6	05 Jan	3
7	06 Jan	3
8	07 Jan	2
9	08 Jan	2
10	09 Jan	2
11	10 Jan	2
12	11 Jan	2
13	12 Jan	0
14	13 Jan	1
15	14 Jan	1
16	15 Jan	1
17	16 Jan	1
18	17 Jan	1
19	18 Jan	0
20	19 Jan	1
21	20 Jan	1
22	21 Jan	0
23	22 Jan	0
24	23 Jan	0
25	24 Jan	0
26	25 Jan	0
27	26 Jan	1
28	27 Jan	1
29	28 Jan	2
30	29 Jan	3
31	30 Jan	7
32	31 Jan	8

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

I achieved this by "brute force", as follows,

• creating a 31 column range with the individual dates as the column headers
• use a formula to check if the date in the header is in between Start Date and End Date
• count the number of "TRUE" cases found for each date
• Transpose into a new sheet for display purposes

There must be a more elegant solution as the above approach takes time to recalculate after each set of entries.

Any help would be appreciated.

Thank you.

 

[TrumpExcel]

Try This
=Sumproduct(--(A2>=Data!B2:B19),--(A2<Data!B2:B19))

 

[TrumpExcel]

For some weird issue, the formula got truncated.. here is the full one

=Sumproduct(--(A2>=Data!B2:B19),--(A2< <data!b2:b19))< html=""></data!b2:b19))<>Data!B2:B19))

 

[JosephGiri]

For some weird issue, the formula got truncated.. here is the full one

=Sumproduct(--(A2>=Data!B2:B19),--(A2< <data!b2:b19))< html=""></data!b2:b19))<>Data!B2:B19))

Works brilliantly. Thank you so very much.

What is the significance of the "--" in the formula?

 

[TrumpExcel]

a double negative (--) converts True to 1 and False to 0

So --(True) becomes 1 and --(False) becomes 0

 

[JosephGiri]

a double negative (--) converts True to 1 and False to 0

So --(True) becomes 1 and --(False) becomes 0

Thank you!