Identifying how many days in month between 2 dates

[Akradon]

Hi all,

Hoping you can help me. Bit of background to help explain what I'm after.

If I had a holiday on 30th August until 1st September. Which is 3 days. But is there a formula where I can make it state that I had 2 days in August and 1 day in September?

I want to have a table with all the months across the top, names down the left hand side, and the body of the table will be filled in with who has had what holiday in which months.

Hope I've explained that correctly.

Thanks in advance guys.

 

[Akradon]

A possible solution

Dates dd/mm/yyyy
Create an auxiliary sheet, say Sheet3, like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[TD="bgcolor: #DCE6F1"]

O​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Employee​

[/TD]
[TD]

Date From​

[/TD]
[TD]

Date to​

[/TD]
[TD]

01/01/2017​

[/TD]
[TD]

01/02/2017​

[/TD]
[TD]

01/03/2017​

[/TD]
[TD]

01/04/2017​

[/TD]
[TD]

01/05/2017​

[/TD]
[TD]

01/06/2017​

[/TD]
[TD]

01/07/2017​

[/TD]
[TD]

01/08/2017​

[/TD]
[TD]

01/09/2017​

[/TD]
[TD]

01/10/2017​

[/TD]
[TD]

01/11/2017​

[/TD]
[TD]

01/12/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Adrian Raducea​

[/TD]
[TD]

31/07/2017​

[/TD]
[TD]

11/08/2017​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

11​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Adrian Raducea​

[/TD]
[TD]

30/08/2017​

[/TD]
[TD]

01/09/2017​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Alan Clark​

[/TD]
[TD]

10/08/2017​

[/TD]
[TD]

31/08/2017​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

22​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Alan Clark​

[/TD]
[TD]

04/07/2017​

[/TD]
[TD]

05/08/2017​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

28​

[/TD]
[TD]

5​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
</tbody>[/TABLE]


Use the same formula (post above):
D2 copied across and down
=MAX(0,1+MIN(EOMONTH(D$1,0),$C2)-MAX(D$1,$B2))

Then in Sheet1...

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[TD="bgcolor: #DCE6F1"]

O​

[/TD]
[TD="bgcolor: #DCE6F1"]

P​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Employee​

[/TD]
[TD]

Entitlement​

[/TD]
[TD]

C/F​

[/TD]
[TD]

Total​

[/TD]
[TD]

01/01/2017​

[/TD]
[TD]

01/02/2017​

[/TD]
[TD]

01/03/2017​

[/TD]
[TD]

01/04/2017​

[/TD]
[TD]

01/05/2017​

[/TD]
[TD]

01/06/2017​

[/TD]
[TD]

01/07/2017​

[/TD]
[TD]

01/08/2017​

[/TD]
[TD]

01/09/2017​

[/TD]
[TD]

01/10/2017​

[/TD]
[TD]

01/11/2017​

[/TD]
[TD]

01/12/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Adrian Raducea​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

13​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Alan Clark​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

28​

[/TD]
[TD]

27​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula E2 copied across and down
=SUMIF(Sheet3!$A:$A,$A2,INDEX(Sheet3!$D:$O,0,MATCH(E$1,Sheet3!$D$1:$O$1,0)))

Done!

M.

You're the man! I will have a try of this first thing Tuesday morning as I'm currently away from my work equipment right now. I'll let you know how it turns out.