Determine if a month falls between two dates.
 

Hello, I have been tasked with trying to do the following

Column A - Date
column B -Date

A will always be earlier than B

columns C-N will be labeled months Jan-Dec

What I need to do is determine if the date between column A and B includes
the given month in column C - N and return a value of X.

ie...
A B C D E F
Jan Feb Mar Apr
1/1/10 3/12/10 X X X blank

I can then count the X values.

Any help would be greatly appreciated.
--
Thank you for your assistance!
Rob

Answer: Determine if a month falls between two dates.
 

Hi Rob,

I'd be happy to help you with this task in Excel!

Here are the steps to determine if a month falls between two dates and return a value of X:

1. In cell C2, enter the formula:
   Formula:

   ---

   =IF(AND(MONTH($A2)<=MONTH(C$1),MONTH($B2)=MONTH(C$1)),"X","") 


   ---

2. Copy the formula in cell C2 to the range C2:N2. You can do this by selecting cell C2, then dragging the fill handle (the small square at the bottom right corner of the cell) to the right until you reach column N.
   
3. Copy the formula in row 2 to the remaining rows in the table. You can do this by selecting cell C2, then dragging the fill handle down to the last row of your table.

This formula uses the IF function to check if the month in the column header (C1:N1) falls between the start date (column A) and end date (column B) for each row. If the month falls between the start and end dates, the formula returns "X". Otherwise, it returns an empty string ("").

Determine if a month falls between two dates.
 

Column A - Date
column B -Date
A will always be earlier than B

Are the dates all within the same year? Will you ever have dates like this:

A2 = 6/25/2010
B2 = 1/12/2011

--
Biff
Microsoft Excel MVP


"Robert R" wrote in message
...
Hello, I have been tasked with trying to do the following

Column A - Date
column B -Date

A will always be earlier than B

columns C-N will be labeled months Jan-Dec

What I need to do is determine if the date between column A and B includes
the given month in column C - N and return a value of X.

ie...
A B C D E F
Jan Feb Mar Apr
1/1/10 3/12/10 X X X blank

I can then count the X values.

Any help would be greatly appreciated.
--
Thank you for your assistance!
Rob

Determine if a month falls between two dates.
 

I would do this in two steps:
1. Put actual dates in C1 to N1. Use a format of "mmm" which will display
the 3-character month name.
2. To get the X's, use:
=if(and(month(c$1)=month($a2),month(c$1)<=month($ b2)),"X","")

Regards,
Fred

"Robert R" wrote in message
...
Hello, I have been tasked with trying to do the following

Column A - Date
column B -Date

A will always be earlier than B

columns C-N will be labeled months Jan-Dec

What I need to do is determine if the date between column A and B includes
the given month in column C - N and return a value of X.

ie...
A B C D E F
Jan Feb Mar Apr
1/1/10 3/12/10 X X X blank

I can then count the X values.

Any help would be greatly appreciated.
--
Thank you for your assistance!
Rob

Determine if a month falls between two dates.
 

T. Valko,

Actually, the dates should always be in the same year
--
Thank you for your assistance!
Rob


"T. Valko" wrote:

Column A - Date
column B -Date
A will always be earlier than B

Are the dates all within the same year? Will you ever have dates like this:

A2 = 6/25/2010
B2 = 1/12/2011

--
Biff
Microsoft Excel MVP


"Robert R" wrote in message
...
Hello, I have been tasked with trying to do the following

Column A - Date
column B -Date

A will always be earlier than B

columns C-N will be labeled months Jan-Dec

What I need to do is determine if the date between column A and B includes
the given month in column C - N and return a value of X.

ie...
A B C D E F
Jan Feb Mar Apr
1/1/10 3/12/10 X X X blank

I can then count the X values.

Any help would be greatly appreciated.
--
Thank you for your assistance!
Rob


.

Determine if a month falls between two dates.
 

Fred's suggestion will work.

Here's another method that is basically the same but doesn't require the
column headers to be dates.

C1:N1 = month names as text entries: Jan, Feb, Mar, ... Dec

A2:B2 = some dates

Enter this formula in C2 and copy across to N2:

=IF(AND(COLUMNS($C2:C2)=MONTH($A2),COLUMNS($C2:C2 )<=MONTH($B2)),"X","")

--
Biff
Microsoft Excel MVP


"Robert R" wrote in message
...
T. Valko,

Actually, the dates should always be in the same year
--
Thank you for your assistance!
Rob


"T. Valko" wrote:

Column A - Date
column B -Date
A will always be earlier than B

Are the dates all within the same year? Will you ever have dates like
this:

A2 = 6/25/2010
B2 = 1/12/2011

--
Biff
Microsoft Excel MVP


"Robert R" wrote in message
...
Hello, I have been tasked with trying to do the following

Column A - Date
column B -Date

A will always be earlier than B

columns C-N will be labeled months Jan-Dec

What I need to do is determine if the date between column A and B
includes
the given month in column C - N and return a value of X.

ie...
A B C D E F
Jan Feb Mar Apr
1/1/10 3/12/10 X X X blank

I can then count the X values.

Any help would be greatly appreciated.
--
Thank you for your assistance!
Rob


.

Hi Biff

Can you please help with similar scenario but the year are different
for example

A2 = 6/25/2010
B2 = 1/12/2011

Thanks