[SOLVED] count between a date range


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

 
 
Thread Tools Rate Thread Display Modes
  #1  
Old 2nd June 2010, 12:20
Dave's Avatar
Dave
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default count between a date range
     Excel: General   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: General   

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



The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #2  
Old 2nd June 2010, 12:30
Jacob Skaria's Avatar
Jacob Skaria
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default RE: count between a date range

------ Register to get rid of these "In Post" ads! ------


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 2nd June 2010, 12:46
Dave's Avatar
Dave
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default RE: count between a date range

------ Register to get rid of these "In Post" ads! ------


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




The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #4  
Old 2nd June 2010, 13:01
Jacob Skaria's Avatar
Jacob Skaria
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default RE: count between a date range

------ Register to get rid of these "In Post" ads! ------


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 2nd June 2010, 13:17
Dave's Avatar
Dave
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default RE: count between a date range

------ Register to get rid of these "In Post" ads! ------


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 2nd June 2010, 13:25
Jacob Skaria's Avatar
Jacob Skaria
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default RE: count between a date range

------ Register to get rid of these "In Post" ads! ------


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




The Code Cage Advertisment
Advertisement
  #7  
Old 2nd June 2010, 13:27
Roger Govier's Avatar
Roger Govier
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: count between a date range

------ Register to get rid of these "In Post" ads! ------


Hi Dave

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

--

Regards
Roger Govier

"Dave" <Dave@discussions.microsoft.com> wrote in message
news:ECB265F0-3761-4307-9129-B5C4DDA55F0F@microsoft.com...
> 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 2nd June 2010, 13:31
Dave's Avatar
Dave
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default RE: count between a date range

------ Register to get rid of these "In Post" ads! ------


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 2nd June 2010, 13:41
Jacob Skaria's Avatar
Jacob Skaria
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default RE: count between a date range

------ Register to get rid of these "In Post" ads! ------


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




The Code Cage Advertisment
Advertisement
  #10  
Old 2nd June 2010, 14:38
Dave's Avatar
Dave
Newsgroup Contributor
 
Posts: n/a
Posts Per Day: 0
My Top Tip Count:
Default Re: count between a date range

------ Register to get rid of these "In Post" ads! ------


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" <Dave@discussions.microsoft.com> wrote in message
> news:ECB265F0-3761-4307-9129-B5C4DDA55F0F@microsoft.com...
> > 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
>
>
>
> .
>




The Code Cage Advertisment
Advertisement
 
The Code Cage at Facebookhe Code Cage at Twitter


Tags
count, date, range

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

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

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 04:41.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University
X vBulletin 3.8.7 Debug Information
  • Page Generation 0.22228 seconds
  • Memory Usage 6,548KB
  • Queries Executed 19 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footerEnd
  • (1)ad_footerStart
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_headerEnd
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_leftcolumn_code
  • (1)ad_navbar_below
  • (5)ad_postbit_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (10)helpful_answers
  • (1)helpfulanswers_javascript
  • (1)navbar
  • (3)navbar_link
  • (3)navbar_noticebit
  • (52)option
  • (10)post_thanks_box
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (10)post_thanks_postbit_info
  • (10)postbit
  • (10)postbit_ctoptip
  • (10)postbit_wrapper
  • (1)showthread_changepostsowner_link
  • (1)showthread_extra_fields
  • (1)showthread_quickreply_mrgdp
  • (1)showthread_reason_popup
  • (1)spacer_close
  • (1)spacer_open
  • (1)su_form
  • (3)tagbit
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_datastore.php
  • ./includes/class_hook.php
  • ./awcoding/plugins/awc.php
  • ./awcoding/plugins/ds.php
  • ./awcoding/plugins/ss.php
  • ./includes/functions_notice.php
  • ./mobiquo/smartbanner.php
  • ./mobiquo/include/classTTConnection.php
  • ./mobiquo/smartbanner/head.inc.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/vbatags.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php
  • ./includes/functions_misc.php
  • ./includes/functions_prefix.php 

Hooks Called:
  • init_startup
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_check_criteria
  • notices_noticebit
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • helpfulanswers_showthreadorpost
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • helpfulanswers_show_normal
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • error_fetch
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete