Prev: Macro Code
Next: Date format
From: Stross002 on 20 Apr 2010 09:17
I formatted a date from 4/20/2010 to April-10 I still have a hard time
filtering
my data.

Eventhough it says April-10 it is still reading the day because the original
date had the day in it. So it is filtering the days seperatly. If I have
two dates: 4/19/2010 in row 1 and 4/20/2010 in row 2, I want to change their
format to MM-YY so I would only count 1 but it is currently counting the two
dates seperatly in my pivot table. Please help me....

From: Ron Rosenfeld on 20 Apr 2010 14:01
On Tue, 20 Apr 2010 06:17:01 -0700, Stross002
<Stross002(a)discussions.microsoft.com> wrote:

>I formatted a date from 4/20/2010 to April-10 I still have a hard time
>filtering
>my data.
>
>Eventhough it says April-10 it is still reading the day because the original
>date had the day in it. So it is filtering the days seperatly. If I have
>two dates: 4/19/2010 in row 1 and 4/20/2010 in row 2, I want to change their
>format to MM-YY so I would only count 1 but it is currently counting the two
>dates seperatly in my pivot table. Please help me....

The filter acts on the contents of the cell. Formatting changes how those
contents is displayed, but it does not change the contents.

If you want to set up a Filter that will act on April 2010, one way would be to
use a "helper column". In that column insert some formula which will convert
all of the April 2010 entries to be the same; then filter on that column.

For example, assuming data starts in A1, and your "helper column" is G:

g1: =date(year(a1),month(a1),1)

Then fill down as far as required.

When you select the filter, select it in Column G; and filter on 04/01/2010
--ron
 | 
Pages: 1
Prev: Macro Code
Next: Date format