COUNT BETWEEN A SPECIFIC DATE RANGE
in [General]
|
Prev: Paste Values in linked Cells Only
Next: Email a single Excel worksheet instead of the whole workbook.
From: Dave on 2 Jun 2010 09:49 Hi. Our yearly calendar runs from 01/11 - 31/10 every year. I have a spreadsheet that contains dates from 01/11/2006 onwards in column a. I would like to be able to mark the dates that are in the year to a date shown on another spreadsheet. So....if the date shown is 25/04/2007, I would like to be able to mark each row with a date between 01/11/2006 and the 25/04/2007. If the date shown is 25/04/2007, then each row with a date from 01/11/2007 to 25/04/2007 and so on. I know this is quite complicated and have tried to think of a practical solution but to no avail. If anyone can help it would be really appreciated. This is an example of the formula i am using at the moment: =IF(AND(M2<=DateCell,M2>=DATE(YEAR(DateCell)-1,MONTH(DateCell),DAY(DateCell))),"Yes","No") Thanks
From: Roger Govier on 2 Jun 2010 10:56 Hi Dave I'm not fully understanding what you are after here. You said that Jacob and my solutions solved your problem. Are you saying, that on say Sheet2, if you have 25/04/2007 in cell A1, then you want A2 onward to show dates from 01/11/2006 until you reach 25/04/2007 in that column? If so, then in A2 enter =DATE(Year($A$1),11,1) In A3 enter =IF(A2+1>$A$1,"",A2+1) Copy down from A4 to A369 -- Regards Roger Govier "Dave" <Dave(a)discussions.microsoft.com> wrote in message news:2F1C064A-A5AC-4BAA-9B7E-07E3459E00E8(a)microsoft.com... > Hi. Our yearly calendar runs from 01/11 - 31/10 every year. > > I have a spreadsheet that contains dates from 01/11/2006 onwards in column > a. I would like to be able to mark the dates that are in the year to a > date > shown on another spreadsheet. > > So....if the date shown is 25/04/2007, I would like to be able to mark > each > row with a date between 01/11/2006 and the 25/04/2007. If the date shown > is > 25/04/2007, then each row with a date from 01/11/2007 to 25/04/2007 and so > on. > > I know this is quite complicated and have tried to think of a practical > solution but to no avail. If anyone can help it would be really > appreciated. > This is an example of the formula i am using at the moment: > > =IF(AND(M2<=DateCell,M2>=DATE(YEAR(DateCell)-1,MONTH(DateCell),DAY(DateCell))),"Yes","No") > > Thanks > > __________ Information from ESET Smart Security, version of virus > signature database 5166 (20100602) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > __________ Information from ESET Smart Security, version of virus signature database 5166 (20100602) __________ The message was checked by ESET Smart Security. http://www.eset.com
From: Dave on 2 Jun 2010 11:45 Hi I explained previously I had another question. Jacob asked me to open a new post as it is dissimilar to the previous post. What i want to do is slightly different. Our yearly calendar runs from 01/11 - 31/10 every year as opposed to 01/01 to 31/12. I want to be able to create a formaula that can mark a row containing a certiian year, upto a certain date. So if you had the date 25/04/2007. The formula would mark the rows which contained dates from the beginning of the year 01/11/2006 to the date specified 25/04/2007 and ignore the others. And similarly, if the date is 25/04/2008, I need the formula to mark all the dates from 01/11/2007 up to 25/04/2008. I can create a formula that will mark all the dates from 01/01 up until whatever date I specify but I am having problems doing this with a different start date for the year. Do you know of a solution to this? Any help would be appreciated. Thanks "Roger Govier" wrote: > Hi Dave > I'm not fully understanding what you are after here. > You said that Jacob and my solutions solved your problem. > > Are you saying, that on say Sheet2, if you have 25/04/2007 in cell A1, then > you want A2 onward to show dates from 01/11/2006 until you reach 25/04/2007 > in that column? > > If so, then in A2 enter > =DATE(Year($A$1),11,1) > In A3 enter > =IF(A2+1>$A$1,"",A2+1) > Copy down from A4 to A369 > > -- > > Regards > Roger Govier > > "Dave" <Dave(a)discussions.microsoft.com> wrote in message > news:2F1C064A-A5AC-4BAA-9B7E-07E3459E00E8(a)microsoft.com... > > Hi. Our yearly calendar runs from 01/11 - 31/10 every year. > > > > I have a spreadsheet that contains dates from 01/11/2006 onwards in column > > a. I would like to be able to mark the dates that are in the year to a > > date > > shown on another spreadsheet. > > > > So....if the date shown is 25/04/2007, I would like to be able to mark > > each > > row with a date between 01/11/2006 and the 25/04/2007. If the date shown > > is > > 25/04/2007, then each row with a date from 01/11/2007 to 25/04/2007 and so > > on. > > > > I know this is quite complicated and have tried to think of a practical > > solution but to no avail. If anyone can help it would be really > > appreciated. > > This is an example of the formula i am using at the moment: > > > > =IF(AND(M2<=DateCell,M2>=DATE(YEAR(DateCell)-1,MONTH(DateCell),DAY(DateCell))),"Yes","No") > > > > Thanks > > > > __________ Information from ESET Smart Security, version of virus > > signature database 5166 (20100602) __________ > > > > The message was checked by ESET Smart Security. > > > > http://www.eset.com > > > > > > > > __________ Information from ESET Smart Security, version of virus signature database 5166 (20100602) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > >
From: Jacob Skaria on 2 Jun 2010 12:10 With the specific date in cell C1 try the below in cell B1 and copy down as required.. =IF(AND(YEAR(A1+61)=YEAR($C$1+61),A1<=$C$1),"Yes","No") -- Jacob (MVP - Excel) "Dave" wrote: > Hi > > I explained previously I had another question. Jacob asked me to open a new > post as it is dissimilar to the previous post. > > What i want to do is slightly different. Our yearly calendar runs from 01/11 > - 31/10 every year as opposed to 01/01 to 31/12. I want to be able to create > a formaula that can mark a row containing a certiian year, upto a certain > date. So if you had the date 25/04/2007. The formula would mark the rows > which contained dates from the beginning of the year 01/11/2006 to the date > specified 25/04/2007 and ignore the others. And similarly, if the date is > 25/04/2008, I need the formula to mark all the dates from 01/11/2007 up to > 25/04/2008. > > I can create a formula that will mark all the dates from 01/01 up until > whatever date I specify but I am having problems doing this with a different > start date for the year. > > Do you know of a solution to this? Any help would be appreciated. > > Thanks > > "Roger Govier" wrote: > > > Hi Dave > > I'm not fully understanding what you are after here. > > You said that Jacob and my solutions solved your problem. > > > > Are you saying, that on say Sheet2, if you have 25/04/2007 in cell A1, then > > you want A2 onward to show dates from 01/11/2006 until you reach 25/04/2007 > > in that column? > > > > If so, then in A2 enter > > =DATE(Year($A$1),11,1) > > In A3 enter > > =IF(A2+1>$A$1,"",A2+1) > > Copy down from A4 to A369 > > > > -- > > > > Regards > > Roger Govier > > > > "Dave" <Dave(a)discussions.microsoft.com> wrote in message > > news:2F1C064A-A5AC-4BAA-9B7E-07E3459E00E8(a)microsoft.com... > > > Hi. Our yearly calendar runs from 01/11 - 31/10 every year. > > > > > > I have a spreadsheet that contains dates from 01/11/2006 onwards in column > > > a. I would like to be able to mark the dates that are in the year to a > > > date > > > shown on another spreadsheet. > > > > > > So....if the date shown is 25/04/2007, I would like to be able to mark > > > each > > > row with a date between 01/11/2006 and the 25/04/2007. If the date shown > > > is > > > 25/04/2007, then each row with a date from 01/11/2007 to 25/04/2007 and so > > > on. > > > > > > I know this is quite complicated and have tried to think of a practical > > > solution but to no avail. If anyone can help it would be really > > > appreciated. > > > This is an example of the formula i am using at the moment: > > > > > > =IF(AND(M2<=DateCell,M2>=DATE(YEAR(DateCell)-1,MONTH(DateCell),DAY(DateCell))),"Yes","No") > > > > > > Thanks > > > > > > __________ Information from ESET Smart Security, version of virus > > > signature database 5166 (20100602) __________ > > > > > > The message was checked by ESET Smart Security. > > > > > > http://www.eset.com > > > > > > > > > > > > > __________ Information from ESET Smart Security, version of virus signature database 5166 (20100602) __________ > > > > The message was checked by ESET Smart Security. > > > > http://www.eset.com > > > > > >
From: Dave on 3 Jun 2010 06:26
This is great! Many thanks for your help!! "Jacob Skaria" wrote: > With the specific date in cell C1 try the below in cell B1 and copy down as > required.. > > =IF(AND(YEAR(A1+61)=YEAR($C$1+61),A1<=$C$1),"Yes","No") > > -- > Jacob (MVP - Excel) > > > "Dave" wrote: > > > Hi > > > > I explained previously I had another question. Jacob asked me to open a new > > post as it is dissimilar to the previous post. > > > > What i want to do is slightly different. Our yearly calendar runs from 01/11 > > - 31/10 every year as opposed to 01/01 to 31/12. I want to be able to create > > a formaula that can mark a row containing a certiian year, upto a certain > > date. So if you had the date 25/04/2007. The formula would mark the rows > > which contained dates from the beginning of the year 01/11/2006 to the date > > specified 25/04/2007 and ignore the others. And similarly, if the date is > > 25/04/2008, I need the formula to mark all the dates from 01/11/2007 up to > > 25/04/2008. > > > > I can create a formula that will mark all the dates from 01/01 up until > > whatever date I specify but I am having problems doing this with a different > > start date for the year. > > > > Do you know of a solution to this? Any help would be appreciated. > > > > Thanks > > > > "Roger Govier" wrote: > > > > > Hi Dave > > > I'm not fully understanding what you are after here. > > > You said that Jacob and my solutions solved your problem. > > > > > > Are you saying, that on say Sheet2, if you have 25/04/2007 in cell A1, then > > > you want A2 onward to show dates from 01/11/2006 until you reach 25/04/2007 > > > in that column? > > > > > > If so, then in A2 enter > > > =DATE(Year($A$1),11,1) > > > In A3 enter > > > =IF(A2+1>$A$1,"",A2+1) > > > Copy down from A4 to A369 > > > > > > -- > > > > > > Regards > > > Roger Govier > > > > > > "Dave" <Dave(a)discussions.microsoft.com> wrote in message > > > news:2F1C064A-A5AC-4BAA-9B7E-07E3459E00E8(a)microsoft.com... > > > > Hi. Our yearly calendar runs from 01/11 - 31/10 every year. > > > > > > > > I have a spreadsheet that contains dates from 01/11/2006 onwards in column > > > > a. I would like to be able to mark the dates that are in the year to a > > > > date > > > > shown on another spreadsheet. > > > > > > > > So....if the date shown is 25/04/2007, I would like to be able to mark > > > > each > > > > row with a date between 01/11/2006 and the 25/04/2007. If the date shown > > > > is > > > > 25/04/2007, then each row with a date from 01/11/2007 to 25/04/2007 and so > > > > on. > > > > > > > > I know this is quite complicated and have tried to think of a practical > > > > solution but to no avail. If anyone can help it would be really > > > > appreciated. > > > > This is an example of the formula i am using at the moment: > > > > > > > > =IF(AND(M2<=DateCell,M2>=DATE(YEAR(DateCell)-1,MONTH(DateCell),DAY(DateCell))),"Yes","No") > > > > > > > > Thanks > > > > > > > > __________ Information from ESET Smart Security, version of virus > > > > signature database 5166 (20100602) __________ > > > > > > > > The message was checked by ESET Smart Security. > > > > > > > > http://www.eset.com > > > > > > > > > > > > > > > > > > __________ Information from ESET Smart Security, version of virus signature database 5166 (20100602) __________ > > > > > > The message was checked by ESET Smart Security. > > > > > > http://www.eset.com > > > > > > > > > |