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 date range



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 12:19 PM posted to microsoft.public.excel.misc
Dave
external usenet poster
 
Posts: 2,331
Default count between a date range

Hi, I want to be able to count between a date range and return the value yes.
I will show you the formula I currently have:

=IF(M2=DateCell-365,"Yes","No")

This gives a yes if the date is greater than the date in M2 minus 365. This
however cincludes all the dates beyond M2 which isnt what I want. I want the
dates upto M2. If that makes sense? Any help would be greatly appreciated

Thanks
  #2  
Old June 2nd, 2010, 12:29 PM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default count between a date range

Try..
=IF(M2=datecell-DATE(1,0,0),"Yes","No")

--
Jacob (MVP - Excel)


"Dave" wrote:

Hi, I want to be able to count between a date range and return the value yes.
I will show you the formula I currently have:

=IF(M2=DateCell-365,"Yes","No")

This gives a yes if the date is greater than the date in M2 minus 365. This
however cincludes all the dates beyond M2 which isnt what I want. I want the
dates upto M2. If that makes sense? Any help would be greatly appreciated

Thanks

  #3  
Old June 2nd, 2010, 12:45 PM posted to microsoft.public.excel.misc
Dave
external usenet poster
 
Posts: 2,331
Default count between a date range

Hi

This does not work. It counts a year back plus one month and gives a yes to
dates beyond the date in M2. So it doesnt work. I have tried to manipulate it
but to no avail!?

Thanks

"Dave" wrote:

Hi, I want to be able to count between a date range and return the value yes.
I will show you the formula I currently have:

=IF(M2=DateCell-365,"Yes","No")

This gives a yes if the date is greater than the date in M2 minus 365. This
however cincludes all the dates beyond M2 which isnt what I want. I want the
dates upto M2. If that makes sense? Any help would be greatly appreciated

Thanks

  #4  
Old June 2nd, 2010, 12:58 PM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default count between a date range

Probably lost here...Can you post back with samples and the expected results...
--
Jacob (MVP - Excel)


"Dave" wrote:

Hi

This does not work. It counts a year back plus one month and gives a yes to
dates beyond the date in M2. So it doesnt work. I have tried to manipulate it
but to no avail!?

Thanks

"Dave" wrote:

Hi, I want to be able to count between a date range and return the value yes.
I will show you the formula I currently have:

=IF(M2=DateCell-365,"Yes","No")

This gives a yes if the date is greater than the date in M2 minus 365. This
however cincludes all the dates beyond M2 which isnt what I want. I want the
dates upto M2. If that makes sense? Any help would be greatly appreciated

Thanks

  #5  
Old June 2nd, 2010, 01:14 PM posted to microsoft.public.excel.misc
Dave
external usenet poster
 
Posts: 2,331
Default count between a date range

OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I
would like to mark the ones which are one year before the date shown in a
seperate sheet. So if the date shown is 01/02/2010, I would like the formula
to mark every row with a date inbetween 01/02/2009 and the 01/02/2010.
Nothing before or after. Does that make sense?

Thanks

"Jacob Skaria" wrote:

Probably lost here...Can you post back with samples and the expected results...
--
Jacob (MVP - Excel)


"Dave" wrote:

Hi

This does not work. It counts a year back plus one month and gives a yes to
dates beyond the date in M2. So it doesnt work. I have tried to manipulate it
but to no avail!?

Thanks

"Dave" wrote:

Hi, I want to be able to count between a date range and return the value yes.
I will show you the formula I currently have:

=IF(M2=DateCell-365,"Yes","No")

This gives a yes if the date is greater than the date in M2 minus 365. This
however cincludes all the dates beyond M2 which isnt what I want. I want the
dates upto M2. If that makes sense? Any help would be greatly appreciated

Thanks

  #6  
Old June 2nd, 2010, 01:22 PM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default count between a date range

Try
=IF(AND(M2=DateCell,M2=DateCell-365),"Yes","No")


--
Jacob (MVP - Excel)


"Dave" wrote:

OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I
would like to mark the ones which are one year before the date shown in a
seperate sheet. So if the date shown is 01/02/2010, I would like the formula
to mark every row with a date inbetween 01/02/2009 and the 01/02/2010.
Nothing before or after. Does that make sense?

Thanks

"Jacob Skaria" wrote:

Probably lost here...Can you post back with samples and the expected results...
--
Jacob (MVP - Excel)


"Dave" wrote:

Hi

This does not work. It counts a year back plus one month and gives a yes to
dates beyond the date in M2. So it doesnt work. I have tried to manipulate it
but to no avail!?

Thanks

"Dave" wrote:

Hi, I want to be able to count between a date range and return the value yes.
I will show you the formula I currently have:

=IF(M2=DateCell-365,"Yes","No")

This gives a yes if the date is greater than the date in M2 minus 365. This
however cincludes all the dates beyond M2 which isnt what I want. I want the
dates upto M2. If that makes sense? Any help would be greatly appreciated

Thanks

  #7  
Old June 2nd, 2010, 01:23 PM posted to microsoft.public.excel.misc
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default count between a date range

Hi Dave

Try
=IF(AND(M2=datecell,
M2=DATE(YEAR(datecell)-1,MONTH(datecell),DAY(datecell))),"Yes","No")

--

Regards
Roger Govier

"Dave" wrote in message
...
OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I
would like to mark the ones which are one year before the date shown in a
seperate sheet. So if the date shown is 01/02/2010, I would like the
formula
to mark every row with a date inbetween 01/02/2009 and the 01/02/2010.
Nothing before or after. Does that make sense?

Thanks

"Jacob Skaria" wrote:

Probably lost here...Can you post back with samples and the expected
results...
--
Jacob (MVP - Excel)


"Dave" wrote:

Hi

This does not work. It counts a year back plus one month and gives a
yes to
dates beyond the date in M2. So it doesnt work. I have tried to
manipulate it
but to no avail!?

Thanks

"Dave" wrote:

Hi, I want to be able to count between a date range and return the
value yes.
I will show you the formula I currently have:

=IF(M2=DateCell-365,"Yes","No")

This gives a yes if the date is greater than the date in M2 minus
365. This
however cincludes all the dates beyond M2 which isnt what I want. I
want the
dates upto M2. If that makes sense? Any help would be greatly
appreciated

Thanks


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

The message was checked by ESET Smart Security.

http://www.eset.com




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

The message was checked by ESET Smart Security.

http://www.eset.com



  #8  
Old June 2nd, 2010, 01:30 PM posted to microsoft.public.excel.misc
Dave
external usenet poster
 
Posts: 2,331
Default count between a date range

Thats great! Thank you! I do have one more question. Should I open another
post?

Thanks
David

"Jacob Skaria" wrote:

Try
=IF(AND(M2=DateCell,M2=DateCell-365),"Yes","No")


--
Jacob (MVP - Excel)


"Dave" wrote:

OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I
would like to mark the ones which are one year before the date shown in a
seperate sheet. So if the date shown is 01/02/2010, I would like the formula
to mark every row with a date inbetween 01/02/2009 and the 01/02/2010.
Nothing before or after. Does that make sense?

Thanks

"Jacob Skaria" wrote:

Probably lost here...Can you post back with samples and the expected results...
--
Jacob (MVP - Excel)


"Dave" wrote:

Hi

This does not work. It counts a year back plus one month and gives a yes to
dates beyond the date in M2. So it doesnt work. I have tried to manipulate it
but to no avail!?

Thanks

"Dave" wrote:

Hi, I want to be able to count between a date range and return the value yes.
I will show you the formula I currently have:

=IF(M2=DateCell-365,"Yes","No")

This gives a yes if the date is greater than the date in M2 minus 365. This
however cincludes all the dates beyond M2 which isnt what I want. I want the
dates upto M2. If that makes sense? Any help would be greatly appreciated

Thanks

  #9  
Old June 2nd, 2010, 01:39 PM posted to microsoft.public.excel.misc
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default count between a date range

If it is not relevant or a continuation to this post then it is better to
open a new post so that contributors can have a go at it as long as the
status remains unanswered....

--
Jacob (MVP - Excel)


"Dave" wrote:

Thats great! Thank you! I do have one more question. Should I open another
post?

Thanks
David

"Jacob Skaria" wrote:

Try
=IF(AND(M2=DateCell,M2=DateCell-365),"Yes","No")


--
Jacob (MVP - Excel)


"Dave" wrote:

OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I
would like to mark the ones which are one year before the date shown in a
seperate sheet. So if the date shown is 01/02/2010, I would like the formula
to mark every row with a date inbetween 01/02/2009 and the 01/02/2010.
Nothing before or after. Does that make sense?

Thanks

"Jacob Skaria" wrote:

Probably lost here...Can you post back with samples and the expected results...
--
Jacob (MVP - Excel)


"Dave" wrote:

Hi

This does not work. It counts a year back plus one month and gives a yes to
dates beyond the date in M2. So it doesnt work. I have tried to manipulate it
but to no avail!?

Thanks

"Dave" wrote:

Hi, I want to be able to count between a date range and return the value yes.
I will show you the formula I currently have:

=IF(M2=DateCell-365,"Yes","No")

This gives a yes if the date is greater than the date in M2 minus 365. This
however cincludes all the dates beyond M2 which isnt what I want. I want the
dates upto M2. If that makes sense? Any help would be greatly appreciated

Thanks

  #10  
Old June 2nd, 2010, 02:35 PM posted to microsoft.public.excel.misc
Dave
external usenet poster
 
Posts: 2,331
Default count between a date range

This works just as well! Thanks alot!

"Roger Govier" wrote:

Hi Dave

Try
=IF(AND(M2=datecell,
M2=DATE(YEAR(datecell)-1,MONTH(datecell),DAY(datecell))),"Yes","No")

--

Regards
Roger Govier

"Dave" wrote in message
...
OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I
would like to mark the ones which are one year before the date shown in a
seperate sheet. So if the date shown is 01/02/2010, I would like the
formula
to mark every row with a date inbetween 01/02/2009 and the 01/02/2010.
Nothing before or after. Does that make sense?

Thanks

"Jacob Skaria" wrote:

Probably lost here...Can you post back with samples and the expected
results...
--
Jacob (MVP - Excel)


"Dave" wrote:

Hi

This does not work. It counts a year back plus one month and gives a
yes to
dates beyond the date in M2. So it doesnt work. I have tried to
manipulate it
but to no avail!?

Thanks

"Dave" wrote:

Hi, I want to be able to count between a date range and return the
value yes.
I will show you the formula I currently have:

=IF(M2=DateCell-365,"Yes","No")

This gives a yes if the date is greater than the date in M2 minus
365. This
however cincludes all the dates beyond M2 which isnt what I want. I
want the
dates upto M2. If that makes sense? Any help would be greatly
appreciated

Thanks


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

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 5165 (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 12:42 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.