If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
COUNT BETWEEN A SPECIFIC DATE RANGE
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 |
#2
|
|||
|
|||
COUNT BETWEEN A SPECIFIC DATE RANGE
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" wrote in message ... 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 |
#3
|
|||
|
|||
COUNT BETWEEN A SPECIFIC DATE RANGE
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" wrote in message ... 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 |
#4
|
|||
|
|||
COUNT BETWEEN A SPECIFIC DATE RANGE
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" wrote in message ... 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 |
#5
|
|||
|
|||
COUNT BETWEEN A SPECIFIC DATE RANGE
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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|