A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

COUNT BETWEEN A SPECIFIC DATE RANGE



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 02:49 PM posted to microsoft.public.excel.misc
Dave
external usenet poster
 
Posts: 2,331
Default 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  
Old June 2nd, 2010, 03:56 PM posted to microsoft.public.excel.misc
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default 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  
Old June 2nd, 2010, 04:45 PM posted to microsoft.public.excel.misc
Dave
external usenet poster
 
Posts: 2,331
Default 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  
Old June 2nd, 2010, 05:10 PM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default 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  
Old June 3rd, 2010, 11:26 AM posted to microsoft.public.excel.misc
Dave
external usenet poster
 
Posts: 2,331
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.