#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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!

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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 ?
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default Date function

Hi Claus,

I wrote 1.5.2011 and formatted it.It worked.
Thanks a lot !!
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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.
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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 !!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Function to lookup date on tab in excel and populate date on calen MGC Excel Worksheet Functions 0 February 4th 10 05:48 AM
Howw can I make a function return a date in date format Jan Kronsell Excel Programming 12 January 21st 10 05:24 PM
Function Excel:If a Dateactual date create a RED alert in a cel? Claudio Nacif Excel Programming 1 June 4th 07 04:00 AM
Date Function formula that will return the date of a specific week Greg Excel Worksheet Functions 4 June 12th 06 05:07 PM
Calculating days between current date and a date in future NETWORKDAYS() function Faheem Khan Excel Worksheet Functions 2 February 10th 05 08:18 PM


All times are GMT +1. The time now is 04:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"