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.

 

[mart37]

Where do you place the holidays with the names?

 

[Marcelo Branco]

Welcome to Mr Excel

Try something like this - try to adapt it to your data setup

Dates as dd/mm/yyyy
First day of each month in D1:O1 (custom format as mmm/yy if desired: jan/17, feb/17....)
Holidays start/end dates in columns B:C

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/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]

HolidayStart​

[/TD]
[TD]

HolidayEnd
​

[/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]

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"]

3
​

[/TD]
[TD]

04/07/2017​

[/TD]
[TD]

04/07/2017
​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

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


Formula in D2 copied across and down
=MAX(0,1+MIN(EOMONTH(D$1,0),$C2)-MAX(D$1,$B2))

Format as number with 0 decimal places

Hope this helps

M.

 

[Akradon]

The information is on Sheet2 and I want the days to be displayed on Sheet1.

It will state on Sheet2, In column B, the first day of the holiday and in Column E the last day of their holiday. Then on Sheet1, I want it to display the amount of days per month they have had off.

 

[Marcelo Branco]

The information is on Sheet2 and I want the days to be displayed on Sheet1.

It will state on Sheet2, In column B, the first day of the holiday and in Column E the last day of their holiday. Then on Sheet1, I want it to display the amount of days per month they have had off.

Only one holiday in Sheet2 or a list of holidays? If the later please provide the list.

M.

 

[Akradon]

Welcome to Mr Excel

Try something like this - try to adapt it to your data setup

Dates as dd/mm/yyyy
First day of each month in D1:O1 (custom format as mmm/yy if desired: jan/17, feb/17....)
Holidays start/end dates in columns B:C

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/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]

HolidayStart​

[/TD]
[TD]

HolidayEnd
​

[/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]

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"]

3
​

[/TD]
[TD]

04/07/2017​

[/TD]
[TD]

04/07/2017
​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

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


Formula in D2 copied across and down
=MAX(0,1+MIN(EOMONTH(D$1,0),$C2)-MAX(D$1,$B2))

Format as number with 0 decimal places

Hope this helps

M.

I thought this worked then. But I've come across a problem of that it is including weekends and bank holidays Just trying to upload a screenshot to show what I'm looking at.

 

[Akradon]

This is the sheet (sheet2)where I enter the information: https://1drv.ms/i/s!AjWq2tLWrX_orlEg0-x5InqJdAI0

This is the sheet (Sheet1) where I want the summary to be: https://1drv.ms/i/s!AjWq2tLWrX_orlAYkBDyAet78kYT

 

[Marcelo Branco]

hmm...looking at the images, i think you would have to radically change your data setup (Sheet2) to use the formula i suggested.
Difficult to help.

M.

 

[Akradon]

Thought it would be. Been racking my head over it for last couple of days. Think I might just have to make some of it manual entry for them. No hardship.

Thanks for the other formula though!

 

[Marcelo Branco]

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.