Count # of days that fall within time period, but restrict based on conditions

[lzcrss2]

Hi there,

I have a list of 247 weeks in chronological order. They do not align with the calendar weeks. I need to count the # of days that fall within the first week of the calendar month OR the last week of the calendar month.

For example, the first week in my list is 01/06/13 - 01/12/13. This week would be assigned a 2 for # of days that fall within the first week of the calendar month (01/06/13 and 01/07/13), and a 0 for # of days that fall within the last week of the calendar month. There are more examples in the table below. I need a formula in the second 2 columns to calculate these # of days automatically. I've done it once before, but I lost the spreadsheet and apparently have become dumber over time. Help!

First Day of Week	Last Day of Week	# of "First Week of Calendar Month" days	# of "Last Week of Calendar Month" days
01/06/13	01/12/13	2	0
01/13/13 <tbody> </tbody>	01/19/13 <tbody> </tbody>	0	0
01/20/13 <tbody> </tbody>	01/26/13 <tbody> </tbody>	0	2
01/27/13 <tbody> </tbody>	02/02/13 <tbody> </tbody>	2	5
02/03/13 <tbody> </tbody>	02/09/13 <tbody> </tbody>	5	0
02/10/13 <tbody> </tbody>	02/16/13 <tbody> </tbody>	0	0
02/17/13 <tbody> </tbody>	02/23/13 <tbody> </tbody>	0	2
02/24/13 <tbody> </tbody>	03/02/13 <tbody> </tbody>	5	2
03/03/13 <tbody> </tbody>	03/09/13 <tbody> </tbody>	5	0
03/10/13 <tbody> </tbody>	03/16/13 <tbody> </tbody>	0	0
03/17/13 <tbody> </tbody>	03/23/13 <tbody> </tbody>	0	0
03/24/13 <tbody> </tbody>	03/30/13 <tbody> </tbody>	0 <tbody> </tbody>	6 <tbody> </tbody>

<tbody>
</tbody>

 

[footoo]

First column :
=IF(DAY(A2)<=7,8-DAY(A2),IF(DAY(B2)<=7,DAY(B2),0))

Last column :
=IF(DAY(EOMONTH(A2,0))-DAY(A2)<7,DAY(EOMONTH(A2,0))-DAY(A2)+1,IF(DAY(EOMONTH(B2,0))-DAY(B2)<7,DAY(B2)-DAY(EOMONTH(B2,0))+7,0))