Identify months/periods applicable to Date range

[Luce]

I am not sure how is the easiest way to accomplish this task, and I am hoping
for some insight/ideas on a formula that will do the job for me. I have 2
date columns: 1)start date and 2)end date, both formatted with month day
year. This date range may span several months.

For each line item, I need to count the number of open occurrences per
month. I do this manually, by creating the resulting columns I need (shown
below). I am hoping there is a formula that can do this count (or data
evaluation) for me.

Example of Orig File:

Item Start Date End Date
#1 May 05, 2008 Jul 28, 2008
#2 May 29, 2008 Aug 3, 2008
#3 Jun 03, 2008 Sep 20, 2008

Example of desired result (additional columns) based on analysis of the
start and end dates above:

May Jun Jul Aug Sep
Item Count Count Count Count Count
#1 1 1 1 0 0
#2 1 1 1 1 0
#3 0 1 1 1 1

Best regards,
Luce

 

[Bernard Liengme]

I have your original data in A1:C4 (So A1 has value "Item")
The data is a date (any format will do)
In F1:J1 I have more dates: the first of May, first of June, first of
July....
To make my results look like yours I format them with custom format mmm;
they display May, Jun, Jul, ...
In F2 I used =AND(MONTH(F$1)>=MONTH($B2),MONTH(F$1)<=MONTH($C2))
Copies this across and down. This gives
May Jun Jul Aug Sep
TRUE TRUE TRUE FALSE FALSE
TRUE TRUE TRUE TRUE FALSE
FALSE TRUE TRUE TRUE TRUE

But I then changed the formula in F2 to
=- -AND(MONTH(F$1)>=MONTH($B2),MONTH(F$1)<=MONTH($C2))
and copied it across and down to get
May Jun Jul Aug Sep
1 1 1 0 0
1 1 1 1 0
0 1 1 1 1

Note the double negation converts Boolean True/False to 1/0

Alternaticvley I could use:
=(MONTH(F$1)>=MONTH($B2))*(MONTH(F$1)<=MONTH($C2))
best wishes

 

[Luce]

Works like a charm! XOXO
Luce

I have your original data in A1:C4 (So A1 has value "Item")
The data is a date (any format will do)
In F1:J1 I have more dates: the first of May, first of June, first of
July....
To make my results look like yours I format them with custom format mmm;
they display May, Jun, Jul, ...
In F2 I used =AND(MONTH(F$1)>=MONTH($B2),MONTH(F$1)<=MONTH($C2))
Copies this across and down. This gives
May Jun Jul Aug Sep
TRUE TRUE TRUE FALSE FALSE
TRUE TRUE TRUE TRUE FALSE
FALSE TRUE TRUE TRUE TRUE

But I then changed the formula in F2 to
=- -AND(MONTH(F$1)>=MONTH($B2),MONTH(F$1)<=MONTH($C2))
and copied it across and down to get
May Jun Jul Aug Sep
1 1 1 0 0
1 1 1 1 0
0 1 1 1 1

Note the double negation converts Boolean True/False to 1/0

Alternaticvley I could use:
=(MONTH(F$1)>=MONTH($B2))*(MONTH(F$1)<=MONTH($C2))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email