| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count the number of times a specific date appears in a range of ce | Excel Worksheet Functions | |||
| count number occurring within specific date range | Excel Worksheet Functions | |||
| Count Specific word in specific range | Excel Worksheet Functions | |||
| Count If Specific word in specific range | Excel Discussion (Misc queries) | |||
| SPECIFIC WORD COUNT FROM A RANGE | Excel Discussion (Misc queries) | |||
Threaded Mode
