# Calculate days out on sick leave by month

#### PIJim

##### Board Regular
I need to calculate how many days an individual is out on sick leave (including weekend, scheduled days off and holidays).

I need to do this throughout the year and there are many rows of information.

A2 = 1/30/06(1st day out)
B2 = 3/15/06(last day out)
C1 = January // C2 = 2(result)
D1 = February // D2 = 28(result)
E1 = March // E2 = 15(result)

I need a formula to caluculate C2, D2 & E2

Any help???
Thanks

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### barry houdini

##### MrExcel MVP
Try this formula in C2 copied across

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(\$A2&":"&\$B2)),"mmmm")=C\$1))

#### GorD

##### Well-known Member
Barry, wouldn't mind an explanation for that one as I have been trying to work that one out unsuccessfully.

This is as far as I got but it doesn't work

=MIN(\$B\$2,EOMONTH(C1,0))-MAX(\$A\$2,EOMONTH(C1,0)) ( this needs adate in row 1)

Interested in how yours works

#### barry houdini

##### MrExcel MVP
Hi GorD,

If there are dates in C1 etc then you can use

=MAX(0,MIN(\$B\$2,EOMONTH(C1,0))+1-MAX(\$A\$2,EOMONTH(C1,-1)+1))

Clearly my solution above assumes Text in C1

ROW(INDIRECT(\$A2&":"&\$B2)

returns an array of date serial numbers corresponding to all dates between A2 and B2. The TEXT function converts these to "January", "February" etc and SUMPRODUCT simply counts those that are equal to the text in C1, thereby giving a count of the dates in the relevant month.

Of course it takes no account of the year.....

#### dcardno

##### Well-known Member
You'll want to upgrade to XL07, as well - otherwise Barry's formula will crash on dates after June, 2079!

#### barry houdini

##### MrExcel MVP
You'll want to upgrade to XL07, as well - otherwise Barry's formula will crash on dates after June, 2079!

Hi Dean,

That's true - that's one disadvantage of that type of formula - but I'd hazard a guess that PIJim isn't too concerned about dates 70 years in the future.....

Possibly wiser, though, to use the option suggested by GorD with dates in row 1 formatted as mmmm or even mmm yyyy.

Of course it'll cope with dates up to 2083 if you're using 1904 date system

edit: thanks for the PM Dean, made me realise I could amend my original suggestion to

=SUMPRODUCT(--(TEXT(\$A3+ROW(INDIRECT("1:"&\$B3-\$A3+1))-1,"mmmm")=C\$1))

which works for any dates as long as the period between them is less than 179 years (approx)

#### PIJim

##### Board Regular
barry houdini's formula in his first post works perfectly!

THANKS!

Replies
2
Views
86
Replies
9
Views
231
Replies
11
Views
278
Replies
8
Views
404
Replies
0
Views
405

1,171,250
Messages
5,874,661
Members
433,064
Latest member
yankat22

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back