Calculate days out on sick leave by month

[PIJim]

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

 

[barry houdini]

Try this formula in C2 copied across

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

 

[GorD]

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]

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]

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

 

[barry houdini]

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]

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

THANKS!