count between a date range
in [General]
|
Prev: Calculate the amount of time over a permitted amount (12 hours
Next: OK, That helps me change 1 cell to Proper case
From: Jacob Skaria on 2 Jun 2010 08:22 Try =IF(AND(M2<=DateCell,M2>=DateCell-365),"Yes","No") -- Jacob (MVP - Excel) "Dave" wrote: > OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I > would like to mark the ones which are one year before the date shown in a > seperate sheet. So if the date shown is 01/02/2010, I would like the formula > to mark every row with a date inbetween 01/02/2009 and the 01/02/2010. > Nothing before or after. Does that make sense? > > Thanks > > "Jacob Skaria" wrote: > > > Probably lost here...Can you post back with samples and the expected results... > > -- > > Jacob (MVP - Excel) > > > > > > "Dave" wrote: > > > > > Hi > > > > > > This does not work. It counts a year back plus one month and gives a yes to > > > dates beyond the date in M2. So it doesnt work. I have tried to manipulate it > > > but to no avail!? > > > > > > Thanks > > > > > > "Dave" wrote: > > > > > > > Hi, I want to be able to count between a date range and return the value yes. > > > > I will show you the formula I currently have: > > > > > > > > =IF(M2>=DateCell-365,"Yes","No") > > > > > > > > This gives a yes if the date is greater than the date in M2 minus 365. This > > > > however cincludes all the dates beyond M2 which isnt what I want. I want the > > > > dates upto M2. If that makes sense? Any help would be greatly appreciated > > > > > > > > Thanks
From: Roger Govier on 2 Jun 2010 08:23 Hi Dave Try =IF(AND(M2<=datecell, M2>=DATE(YEAR(datecell)-1,MONTH(datecell),DAY(datecell))),"Yes","No") -- Regards Roger Govier "Dave" <Dave(a)discussions.microsoft.com> wrote in message news:ECB265F0-3761-4307-9129-B5C4DDA55F0F(a)microsoft.com... > OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I > would like to mark the ones which are one year before the date shown in a > seperate sheet. So if the date shown is 01/02/2010, I would like the > formula > to mark every row with a date inbetween 01/02/2009 and the 01/02/2010. > Nothing before or after. Does that make sense? > > Thanks > > "Jacob Skaria" wrote: > >> Probably lost here...Can you post back with samples and the expected >> results... >> -- >> Jacob (MVP - Excel) >> >> >> "Dave" wrote: >> >> > Hi >> > >> > This does not work. It counts a year back plus one month and gives a >> > yes to >> > dates beyond the date in M2. So it doesnt work. I have tried to >> > manipulate it >> > but to no avail!? >> > >> > Thanks >> > >> > "Dave" wrote: >> > >> > > Hi, I want to be able to count between a date range and return the >> > > value yes. >> > > I will show you the formula I currently have: >> > > >> > > =IF(M2>=DateCell-365,"Yes","No") >> > > >> > > This gives a yes if the date is greater than the date in M2 minus >> > > 365. This >> > > however cincludes all the dates beyond M2 which isnt what I want. I >> > > want the >> > > dates upto M2. If that makes sense? Any help would be greatly >> > > appreciated >> > > >> > > Thanks > > __________ Information from ESET Smart Security, version of virus > signature database 5165 (20100602) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > __________ Information from ESET Smart Security, version of virus signature database 5165 (20100602) __________ The message was checked by ESET Smart Security. http://www.eset.com
From: Dave on 2 Jun 2010 08:30 Thats great! Thank you! I do have one more question. Should I open another post? Thanks David "Jacob Skaria" wrote: > Try > =IF(AND(M2<=DateCell,M2>=DateCell-365),"Yes","No") > > > -- > Jacob (MVP - Excel) > > > "Dave" wrote: > > > OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I > > would like to mark the ones which are one year before the date shown in a > > seperate sheet. So if the date shown is 01/02/2010, I would like the formula > > to mark every row with a date inbetween 01/02/2009 and the 01/02/2010. > > Nothing before or after. Does that make sense? > > > > Thanks > > > > "Jacob Skaria" wrote: > > > > > Probably lost here...Can you post back with samples and the expected results... > > > -- > > > Jacob (MVP - Excel) > > > > > > > > > "Dave" wrote: > > > > > > > Hi > > > > > > > > This does not work. It counts a year back plus one month and gives a yes to > > > > dates beyond the date in M2. So it doesnt work. I have tried to manipulate it > > > > but to no avail!? > > > > > > > > Thanks > > > > > > > > "Dave" wrote: > > > > > > > > > Hi, I want to be able to count between a date range and return the value yes. > > > > > I will show you the formula I currently have: > > > > > > > > > > =IF(M2>=DateCell-365,"Yes","No") > > > > > > > > > > This gives a yes if the date is greater than the date in M2 minus 365. This > > > > > however cincludes all the dates beyond M2 which isnt what I want. I want the > > > > > dates upto M2. If that makes sense? Any help would be greatly appreciated > > > > > > > > > > Thanks
From: Jacob Skaria on 2 Jun 2010 08:39 If it is not relevant or a continuation to this post then it is better to open a new post so that contributors can have a go at it as long as the status remains unanswered.... -- Jacob (MVP - Excel) "Dave" wrote: > Thats great! Thank you! I do have one more question. Should I open another > post? > > Thanks > David > > "Jacob Skaria" wrote: > > > Try > > =IF(AND(M2<=DateCell,M2>=DateCell-365),"Yes","No") > > > > > > -- > > Jacob (MVP - Excel) > > > > > > "Dave" wrote: > > > > > OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I > > > would like to mark the ones which are one year before the date shown in a > > > seperate sheet. So if the date shown is 01/02/2010, I would like the formula > > > to mark every row with a date inbetween 01/02/2009 and the 01/02/2010. > > > Nothing before or after. Does that make sense? > > > > > > Thanks > > > > > > "Jacob Skaria" wrote: > > > > > > > Probably lost here...Can you post back with samples and the expected results... > > > > -- > > > > Jacob (MVP - Excel) > > > > > > > > > > > > "Dave" wrote: > > > > > > > > > Hi > > > > > > > > > > This does not work. It counts a year back plus one month and gives a yes to > > > > > dates beyond the date in M2. So it doesnt work. I have tried to manipulate it > > > > > but to no avail!? > > > > > > > > > > Thanks > > > > > > > > > > "Dave" wrote: > > > > > > > > > > > Hi, I want to be able to count between a date range and return the value yes. > > > > > > I will show you the formula I currently have: > > > > > > > > > > > > =IF(M2>=DateCell-365,"Yes","No") > > > > > > > > > > > > This gives a yes if the date is greater than the date in M2 minus 365. This > > > > > > however cincludes all the dates beyond M2 which isnt what I want. I want the > > > > > > dates upto M2. If that makes sense? Any help would be greatly appreciated > > > > > > > > > > > > Thanks
From: Dave on 2 Jun 2010 09:35
This works just as well! Thanks alot! "Roger Govier" wrote: > Hi Dave > > Try > =IF(AND(M2<=datecell, > M2>=DATE(YEAR(datecell)-1,MONTH(datecell),DAY(datecell))),"Yes","No") > > -- > > Regards > Roger Govier > > "Dave" <Dave(a)discussions.microsoft.com> wrote in message > news:ECB265F0-3761-4307-9129-B5C4DDA55F0F(a)microsoft.com... > > OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I > > would like to mark the ones which are one year before the date shown in a > > seperate sheet. So if the date shown is 01/02/2010, I would like the > > formula > > to mark every row with a date inbetween 01/02/2009 and the 01/02/2010. > > Nothing before or after. Does that make sense? > > > > Thanks > > > > "Jacob Skaria" wrote: > > > >> Probably lost here...Can you post back with samples and the expected > >> results... > >> -- > >> Jacob (MVP - Excel) > >> > >> > >> "Dave" wrote: > >> > >> > Hi > >> > > >> > This does not work. It counts a year back plus one month and gives a > >> > yes to > >> > dates beyond the date in M2. So it doesnt work. I have tried to > >> > manipulate it > >> > but to no avail!? > >> > > >> > Thanks > >> > > >> > "Dave" wrote: > >> > > >> > > Hi, I want to be able to count between a date range and return the > >> > > value yes. > >> > > I will show you the formula I currently have: > >> > > > >> > > =IF(M2>=DateCell-365,"Yes","No") > >> > > > >> > > This gives a yes if the date is greater than the date in M2 minus > >> > > 365. This > >> > > however cincludes all the dates beyond M2 which isnt what I want. I > >> > > want the > >> > > dates upto M2. If that makes sense? Any help would be greatly > >> > > appreciated > >> > > > >> > > Thanks > > > > __________ Information from ESET Smart Security, version of virus > > signature database 5165 (20100602) __________ > > > > The message was checked by ESET Smart Security. > > > > http://www.eset.com > > > > > > > > __________ Information from ESET Smart Security, version of virus signature database 5165 (20100602) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > > . > |