COUNT BETWEEN A SPECIFIC DATE RANGE

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

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
 
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 said:
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
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 5166 (20100602) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
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
 
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")
 
This is great! Many thanks for your help!!

Jacob Skaria said:
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")
Click to expand...
 
Back
Top