Date function
 

Hi,

I have 2 columns of dates , one being the start and the other the end. I need to find out if a certain month falls between a start and an end date.

This is what my data looks like .

Startdate Enddate May-11 Jun-11 July-11........dec-13
2/5/2011 6/10/2011
2/10/2011 6/1/2012
2/4/2011 6/12/2012

Now if may 11 falls in between any of the start or end date i need the cell to display "yes" and so on for each month.

Please help!
Thanks in advance!

Date function
 

On Wednesday, 27 May 2015 17:35:33 UTC+10, Nitya Satheesh wrote:
Hi,

I have 2 columns of dates , one being the start and the other the end. I need to find out if a certain month falls between a start and an end date.

This is what my data looks like .

Startdate Enddate May-11 Jun-11 July-11........dec-13
2/5/2011 6/10/2011
2/10/2011 6/1/2012
2/4/2011 6/12/2012

Now if may 11 falls in between any of the start or end date i need the cell to display "yes" and so on for each month.

Please help!
Thanks in advance!

If the start dates are in column A and the end dates are in column B and the dates across the top are in C1, D1, E1, ..., then enter this formula in cell C2 and then fill down and across:
=IF(AND(C$1=$A2, C$1<=$B2), "Yes", "No")

Howard

Date function
 

On Thursday, May 28, 2015 at 12:17:25 PM UTC+5:30, Howard Silcock wrote:
On Wednesday, 27 May 2015 17:35:33 UTC+10, Nitya Satheesh wrote:
Hi,

I have 2 columns of dates , one being the start and the other the end. I need to find out if a certain month falls between a start and an end date.

This is what my data looks like .

Startdate Enddate May-11 Jun-11 July-11........dec-13
2/5/2011 6/10/2011
2/10/2011 6/1/2012
2/4/2011 6/12/2012

Now if may 11 falls in between any of the start or end date i need the cell to display "yes" and so on for each month.

Please help!
Thanks in advance!

If the start dates are in column A and the end dates are in column B and the dates across the top are in C1, D1, E1, ..., then enter this formula in cell C2 and then fill down and across:
=IF(AND(C$1=$A2, C$1<=$B2), "Yes", "No")

Howard

Thanks Howard! An issue I have is those dates from column c onwards are typed, so I'm not sure Excel recognizes them as dates. How can I solve this ?

Date function
 

Hi Nitya,

Am Thu, 28 May 2015 01:24:24 -0700 (PDT) schrieb Nitya Satheesh:

Thanks Howard! An issue I have is those dates from column c onwards are typed, so I'm not sure Excel recognizes them as dates. How can I solve this ?

try in C TextToColumns. If the entry changes to real date copy it to the
right = Fill Month
Or write 01.05.2011 in C1 and format it MMM-YY and copa to the right


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Date function
 

Hi,

Am Thu, 28 May 2015 10:40:02 +0200 schrieb Claus Busch:

try in C TextToColumns. If the entry changes to real date copy it to the
right = Fill Month
Or write 01.05.2011 in C1 and format it MMM-YY and copa to the right

you can also change your text into a real date with following array
formula:
=DATE(2000+RIGHT(C1,2),MATCH(LEFT(C1,3),TEXT(ROW(1 :12)*28,"MMM"),0),1)
and insert the formula with CTRL+Shift+Enter


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Date function
 

Hi Claus,

I wrote 1.5.2011 and formatted it.It worked.
Thanks a lot !!

Date function
 

On Thursday, May 28, 2015 at 12:17:25 PM UTC+5:30, Howard Silcock wrote:
On Wednesday, 27 May 2015 17:35:33 UTC+10, Nitya Satheesh wrote:
Hi,

I have 2 columns of dates , one being the start and the other the end. I need to find out if a certain month falls between a start and an end date..

This is what my data looks like .

Startdate Enddate May-11 Jun-11 July-11........dec-13
2/5/2011 6/10/2011
2/10/2011 6/1/2012
2/4/2011 6/12/2012

Now if may 11 falls in between any of the start or end date i need the cell to display "yes" and so on for each month.

Please help!
Thanks in advance!

If the start dates are in column A and the end dates are in column B and the dates across the top are in C1, D1, E1, ..., then enter this formula in cell C2 and then fill down and across:
=IF(AND(C$1=$A2, C$1<=$B2), "Yes", "No")

Howard

Hi Howard,

A problem I have with the formula you gave me is that if the start date is 12/5/2011 and the end date is 19/3/2012, it is diplaying "no" under may-11. I need it to display yes if May=11 is present in between the start date and end date. I'm not too worried about the dates, I just need the month. So if a month is present in between the start and end date I need that column to display "yes". What do I do ? Sorry if I haven't been able to explain it better.

Date function
 

hi Nitya,

Am Sun, 31 May 2015 22:50:38 -0700 (PDT) schrieb Nitya Satheesh:

A problem I have with the formula you gave me is that if the start date is 12/5/2011 and the end date is 19/3/2012, it is diplaying "no" under may-11. I need it to display yes if May=11 is present in between the start date and end date. I'm not too worried about the dates, I just need the month. So if a month is present in between the start and end date I need that column to display "yes". What do I do ? Sorry if I haven't been able to explain it better.

in C2 try:
=IF(AND(EOMONTH($A2,-1)+1<=C$1,EOMONTH($B2,0)=C$1),"Yes","")
and copy down and to the right


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Date function
 

hi again,

Am Mon, 1 Jun 2015 08:04:30 +0200 schrieb Claus Busch:

in C2 try:
=IF(AND(EOMONTH($A2,-1)+1<=C$1,EOMONTH($B2,0)=C$1),"Yes","")

better try this:
=IF(AND(EOMONTH($A2,-1)+1<=C$1,$B2=C$1),"Yes","")


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Date function
 

On Monday, June 1, 2015 at 11:46:07 AM UTC+5:30, Claus Busch wrote:
hi again,

Am Mon, 1 Jun 2015 08:04:30 +0200 schrieb Claus Busch:

in C2 try:
=IF(AND(EOMONTH($A2,-1)+1<=C$1,EOMONTH($B2,0)=C$1),"Yes","")

better try this:
=IF(AND(EOMONTH($A2,-1)+1<=C$1,$B2=C$1),"Yes","")


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus,
This fixes my issue.
Thanks a lot !!