Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 4th 07, 03:51 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 15
Default find dates between in excel

I have 2 columns of data: one is name, the other is dates.

BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 3/7/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007
BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007


I'd like to know how many times each name shows up in the month of May (or
whatever month I'm looking for). So, if I need a "findbetween" or some other
formula, I would love some help.

Thanks all, in advance!

  #2   Report Post  
Old June 4th 07, 04:04 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,339
Default find dates between in excel

this will count of BOB for MAY (month 5)

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5))

For a specific month/year

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5),--YEAR(B1:B27)=2007))


You can replace "BOB" with cell containing the text "BOB"

HTH

"MDI Anne" wrote:

I have 2 columns of data: one is name, the other is dates.

BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 3/7/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007
BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007


I'd like to know how many times each name shows up in the month of May (or
whatever month I'm looking for). So, if I need a "findbetween" or some other
formula, I would love some help.

Thanks all, in advance!

  #3   Report Post  
Old June 4th 07, 04:09 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,572
Default find dates between in excel

With names in Column A, and dates in Column B, one way would be to enter a
name to count in C1, and the number of the month (Jan=1, Feb=2, ... etc.) in
C2, and try something like this:

=SUMPRODUCT((A1:A50=C1)*(MONTH(B1:B50)=C2))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"MDI Anne" wrote in message
...
I have 2 columns of data: one is name, the other is dates.

BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 3/7/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007
BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007


I'd like to know how many times each name shows up in the month of May (or
whatever month I'm looking for). So, if I need a "findbetween" or some
other
formula, I would love some help.

Thanks all, in advance!


  #4   Report Post  
Old June 4th 07, 04:38 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 15
Default find dates between in excel

This first formula (for month only) works fine...but the second one (for
month & year) comes up with zeros only. What am I doing wrong? I copied
directly from here to my worksheet....

"Toppers" wrote:

this will count of BOB for MAY (month 5)

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5))

For a specific month/year

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5),--YEAR(B1:B27)=2007))


You can replace "BOB" with cell containing the text "BOB"

HTH

"MDI Anne" wrote:

I have 2 columns of data: one is name, the other is dates.

BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 3/7/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007
BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007


I'd like to know how many times each name shows up in the month of May (or
whatever month I'm looking for). So, if I need a "findbetween" or some other
formula, I would love some help.

Thanks all, in advance!

  #5   Report Post  
Old June 4th 07, 04:38 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 2,202
Default find dates between in excel

With names in Column A, and dates in Column B, one way would be to enter a
name to count in C1, and the number of the month (Jan=1, Feb=2, ... etc.)
in
C2, and try something like this:


Another possibility which is more "visual" (you will be able to see the
matching rows). Assuming the same set up above, put this formula in D1...

=A1&" - "&MONTH(B1)

and copy down to the end of your data. Then put this formula in E1...

=IF(AND(A1=C$1,MONTH(B1)=C$2),COUNTIF(D$1$10000, "="&A1&" - "&C$2),"")

(using a number larger than the number of rows of data you have for the
10000 I used) and copy down to the end of your data (or beyond if your data
can grow). Now, when you put a name in C1 and a month number in C2, each
matching row will be flagged in column 'E' with the total number of hits for
that match.

Rick



  #6   Report Post  
Old June 4th 07, 04:49 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,339
Default find dates between in excel

Sorry ... my typo ....

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5),--(YEAR(B1:B27)=2007))


"MDI Anne" wrote:

This first formula (for month only) works fine...but the second one (for
month & year) comes up with zeros only. What am I doing wrong? I copied
directly from here to my worksheet....

"Toppers" wrote:

this will count of BOB for MAY (month 5)

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5))

For a specific month/year

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5),--YEAR(B1:B27)=2007))


You can replace "BOB" with cell containing the text "BOB"

HTH

"MDI Anne" wrote:

I have 2 columns of data: one is name, the other is dates.

BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 3/7/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007
BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007


I'd like to know how many times each name shows up in the month of May (or
whatever month I'm looking for). So, if I need a "findbetween" or some other
formula, I would love some help.

Thanks all, in advance!

  #7   Report Post  
Old June 4th 07, 06:06 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 15
Default find dates between in excel

Excellent!!! Works like a charm! Thank you!!

"Toppers" wrote:

Sorry ... my typo ....

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5),--(YEAR(B1:B27)=2007))


"MDI Anne" wrote:

This first formula (for month only) works fine...but the second one (for
month & year) comes up with zeros only. What am I doing wrong? I copied
directly from here to my worksheet....

"Toppers" wrote:

this will count of BOB for MAY (month 5)

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5))

For a specific month/year

=SUMPRODUCT(--(A1:A27="BOB"),--(MONTH(B1:B27)=5),--YEAR(B1:B27)=2007))


You can replace "BOB" with cell containing the text "BOB"

HTH

"MDI Anne" wrote:

I have 2 columns of data: one is name, the other is dates.

BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 3/7/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007
BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007


I'd like to know how many times each name shows up in the month of May (or
whatever month I'm looking for). So, if I need a "findbetween" or some other
formula, I would love some help.

Thanks all, in advance!

  #8   Report Post  
Old June 4th 07, 06:25 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 103
Default find dates between in excel

hi anne,
by using filters u could do this.You can find the filter option in
DataFilterAuto Filter. By clicking this you could find dropdowns come in
the first row.There u cold go for custom where u could give two dates and
thus could filter the range of datas to clear the filter click on the
dropdown and give all values.

Regards
Arun

"MDI Anne" wrote:

I have 2 columns of data: one is name, the other is dates.

BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 3/7/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007
BOB 5/1/2007
STEVE 4/19/2007
JOHN 3/22/2007
MARY 7/21/2006
KATHY 5/4/2007
BOB 10/7/2006
STEVE 5/22/2007
MARY 5/14/2007
MARY 5/30/2007
KATHY 1/5/2007
JOHN 5/15/2007
BOB 5/25/2007
STEVE 5/13/2007


I'd like to know how many times each name shows up in the month of May (or
whatever month I'm looking for). So, if I need a "findbetween" or some other
formula, I would love some help.

Thanks all, in advance!



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find future dates LiLi Excel Discussion (Misc queries) 3 September 15th 06 04:38 AM
How do I find the earliest dates in a range of dates? JJ Excel Worksheet Functions 3 May 16th 06 09:36 AM
Excel auto filtering to find a range of dates in a list Candy Excel Discussion (Misc queries) 2 January 31st 06 02:32 PM
to find number of days between 2 dates using vba code in excel sjayar Excel Discussion (Misc queries) 3 November 3rd 05 06:24 AM
Find a "date" in a column of dates in Excel 2000 JR Hester Excel Worksheet Functions 3 November 1st 05 09:17 PM


All times are GMT +1. The time now is 11:44 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017