• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dates display in filters

D

dudeski

New Member
Hi,


This might be a silly question but I can't seem to figure this out on excel 2003 to save my life.


I have a corporate spreadsheet that lists each job by the date it was completed on. There is a filter on the data and it isn't a pivot table.


How can I group the dates in to the month so say when I filter August...all the dates in August are selected?


My apologies for not explaining it very well


Thanks in advance
 
Unfortunately, I don't think you can do that directly. Easiest way is to create a helper column with a formula like this:

=TEXT(A2,"mmmmm")

which will display just a month text for a date, and then you can filter on that column for all the Augusts.
 
You can also use the Custom feature to filter, and select Is greater than or equal to, and enter start date of the month, then select AND and select Is less than or equal to, and enter end date of the month.
 
Hello Dudeski,


Quickly what I can think of is you can use "Text" function to convert dates into respective months.

I assume following is your data set:

Date (at A1) Job (at B1)

8/2/2012 Job1

8/9/2012 Job2

8/10/2012 Job3

8/11/2012 Job4

7/7/2012 Job5

7/8/2012 Job6

8/14/2012 Job7

8/17/2012 Job8

9/14/2012 Job9

9/15/2012 Job10

9/16/2012 Job11

9/17/2012 Job12

8/20/2012 Job13

9/18/2012 Job14

8/25/2012 Job15


Now, at C2 write the formula =TEXT(A2,"mmmm yyyy")

You will get August 2012 at C2. Now, scroll it all the way down to get the respective months (I have used the format "mmmm yyyy", hence you are getting month along with year.However, you can only use the "mmmm" format to get the month name only).

Then you filter the column "C" for "August 2012" and get the respective dates for this month.


Hope this helps.
 
You can also use Advanced Filter.


Assuming A1 is header & A2 to down starts data. Insert 3 blank rows above the heading. Now the heading should be in A4, data starts from A5 to down.


C2, enter the month to filter, you could use a validation list here, to choose the months.


Leave A1 blank, then in A2 enter: =TEXT(A5,"mmmm;;;")=$C$2


C2 use full month like January, if you are using short month like Jan, Feb etc.. then in A2 use 3 m's in A2: =TEXT(A5,"mmm;;;")=$C$2


Or this formula in A2 should allow to use short/full month format, like Jan, Feb, January, February etc..


=ISNUMBER(SEARCH($C$2,TEXT(A5,"mmmm;;;")))


If you want to include years too, enter a year in a cell ie D2, then add yyyy with text function.


=TEXT(A5,"mmmmyyyy")=$C$2&$D$2
 
You must log in or register to reply here.
Back
Top