reporting on leave periods

[LOUBOYBEAR]

Hi All
I need some help constructing a formula.
I need to report on leave days within a month and I get a report which gives totals of leave taken during the month... but some leave starts before the reporting month or ends after the reporting month so I need to break down the leave to count only the days that fall within the month.
the variations I have are:
For reporting Period Aug 2011
Leave commencing before 1 Aug 11 and ending within Aug.
Leave commencing after 1 Aug and ending after Aug
All leave days falling within Aug.

<TABLE dir=ltr borderColor=#000000 cellSpacing=0 cellPadding=2 width=631 border=1><TBODY><TR><TD width="12%" height=19>

Depart cd​

​

</TD><TD width="14%" height=19>

Leave Type​

​

</TD><TD width="14%" height=19>

Start​

​

</TD><TD width="14%" bgColor=#ff99cc height=19>

End​

​

​

​

​

</TD><TD width="17%" height=19>

Hours​

​

</TD><TD width="16%" height=19>

Days​

​

</TD><TD width="13%" height=19>

Certificate​

​

</TD></TR><TR><TD width="12%" height=19>

BHL​

​

</TD><TD width="14%" height=19>

USIC​

​

</TD><TD width="14%" height=19>

1/08/2011​

​

</TD><TD width="14%" bgColor=#ff99cc height=19>

7/10/2011​

​

​

​

​

</TD><TD width="17%" height=19>

100​

​

</TD><TD width="16%" height=19>

50​

​

</TD><TD width="13%" height=19>
</TD></TR><TR><TD width="12%" height=19>

BHL​

​

</TD><TD width="14%" height=19>

SIC​

​

</TD><TD width="14%" bgColor=#ff99cc height=19>

29/07/2011​

​

​

​

​

</TD><TD width="14%" height=19>

5/08/2011​

​

</TD><TD width="17%" height=19>

6​

​

</TD><TD width="16%" height=19>

5​

​

</TD><TD width="13%" height=19>
</TD></TR></TBODY></TABLE>

All suggestions welcome... Thanks

 

[Biz]

Hi,

Input Start date of month ie 01/08/2011 in cell J3

Try Formula below cell F10 and copy down
=NETWORKDAYS(IF(C10<$J$3,$J$3,C10),D10)


Does this help?

Biz

 

[LOUBOYBEAR]

Hi Biz, thanks for your reply. That works well.
just now trying to extend that so that if leave begins in the period and ENDS OUTSIDE the period it stops counting the days at 31/8/11.

 

[barry houdini]

Try this version

=NETWORKDAYS(MAX($J$3,C10),MIN(D10,EOMONTH($J$3,0)))

 

[LOUBOYBEAR]

Thanks for that suggestion Barry, I will give that one a go too. appreciate your time!

 

[Biz]

Try this version

=NETWORKDAYS(MAX($J$3,C10),MIN(D10,EOMONTH($J$3,0)))

Nice formula mate.

Biz