PivotTables - formatting Row fields when grouped
 

I have dates as my row fields.

Problem: I want to group data by week/month/etc., but don't want to graph
the long text that excel spits out (ie 8/29/2005 - 9/4/2005). Instead, I
want to "extract" just the first date of range using LEFT, or some other text
function. This way I can format as date, and then graph (regular chart, not
pivotchart) on a timescale, manipulate data more easily, etc.

However, it appears that no matter how I set the number format in the
original data, or in the field settings of the pivotTable, when I group the
dates the dates come out m/d/yyyy. I don't care what the format, but I want
the dates to have a consistent number of digits(like mm/dd/yyyy) so I can
extract using the LEFT command.

Is there a way to do this? Or am I making this too complicated? Is there
an easier way?

Thank you!
Heidi

PivotTables - formatting Row fields when grouped
 

What do you want to extract from mm/dd/yyyy? The month? Then do =MONTH(A1)

Day: =DAY(A1)

Year: =YEAR(A1)

If I've misunderstood your question, post back.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Heidi" wrote:

I have dates as my row fields.

Problem: I want to group data by week/month/etc., but don't want to graph
the long text that excel spits out (ie 8/29/2005 - 9/4/2005). Instead, I
want to "extract" just the first date of range using LEFT, or some other text
function. This way I can format as date, and then graph (regular chart, not
pivotchart) on a timescale, manipulate data more easily, etc.

However, it appears that no matter how I set the number format in the
original data, or in the field settings of the pivotTable, when I group the
dates the dates come out m/d/yyyy. I don't care what the format, but I want
the dates to have a consistent number of digits(like mm/dd/yyyy) so I can
extract using the LEFT command.

Is there a way to do this? Or am I making this too complicated? Is there
an easier way?

Thank you!
Heidi

PivotTables - formatting Row fields when grouped
 

Dave,

Once I group the fields, Excel converts everything to text (ie the cell
contains: 8/29/2005 - 9/4/2005). I can't do any functions on it, because it
is text. That's why I want to extract just the leftmost 8-10 characters, so
I can force it to be an actual Excel date (ie a number underneath).
However, it won't generate the text with a consistent number of digits (like
mm/dd/yy would). So, sometimes I need the leftmost 8 digits (5/9/2003), and
sometimes the leftmost 10 digits (12/26/2005). I want Excel to generate the
TEXT using mm/dd/yyyy formats (or ANY format with a consistent number of
characters).

Or maybe I need a different approach. I'm not sure.

Thanks,
Heidi

"Dave F" wrote:

What do you want to extract from mm/dd/yyyy? The month? Then do =MONTH(A1)

Day: =DAY(A1)

Year: =YEAR(A1)

If I've misunderstood your question, post back.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Heidi" wrote:

I have dates as my row fields.

Problem: I want to group data by week/month/etc., but don't want to graph
the long text that excel spits out (ie 8/29/2005 - 9/4/2005). Instead, I
want to "extract" just the first date of range using LEFT, or some other text
function. This way I can format as date, and then graph (regular chart, not
pivotchart) on a timescale, manipulate data more easily, etc.

However, it appears that no matter how I set the number format in the
original data, or in the field settings of the pivotTable, when I group the
dates the dates come out m/d/yyyy. I don't care what the format, but I want
the dates to have a consistent number of digits(like mm/dd/yyyy) so I can
extract using the LEFT command.

Is there a way to do this? Or am I making this too complicated? Is there
an easier way?

Thank you!
Heidi

PivotTables - formatting Row fields when grouped
 

Hi Heidi

When I group by Week, Excel outputs in the same setting as for my
Regional settings for the short format of Date, which here in the UK I
have set as dd/mm/yyyy.

I am able to use =LEFT(F5,10) to extract the first date from
08/01/2007 - 14/01/2007

Check your Regional settings through StartControl PanelRegional
settings

--
Regards

Roger Govier


"Heidi" wrote in message
...
I have dates as my row fields.

Problem: I want to group data by week/month/etc., but don't want to
graph
the long text that excel spits out (ie 8/29/2005 - 9/4/2005).
Instead, I
want to "extract" just the first date of range using LEFT, or some
other text
function. This way I can format as date, and then graph (regular
chart, not
pivotchart) on a timescale, manipulate data more easily, etc.

However, it appears that no matter how I set the number format in the
original data, or in the field settings of the pivotTable, when I
group the
dates the dates come out m/d/yyyy. I don't care what the format, but
I want
the dates to have a consistent number of digits(like mm/dd/yyyy) so I
can
extract using the LEFT command.

Is there a way to do this? Or am I making this too complicated? Is
there
an easier way?

Thank you!
Heidi

PivotTables - formatting Row fields when grouped
 

I think I misunderstood your question. Roger's response sounds more accurate.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Heidi" wrote:

Dave,

Once I group the fields, Excel converts everything to text (ie the cell
contains: 8/29/2005 - 9/4/2005). I can't do any functions on it, because it
is text. That's why I want to extract just the leftmost 8-10 characters, so
I can force it to be an actual Excel date (ie a number underneath).
However, it won't generate the text with a consistent number of digits (like
mm/dd/yy would). So, sometimes I need the leftmost 8 digits (5/9/2003), and
sometimes the leftmost 10 digits (12/26/2005). I want Excel to generate the
TEXT using mm/dd/yyyy formats (or ANY format with a consistent number of
characters).

Or maybe I need a different approach. I'm not sure.

Thanks,
Heidi

"Dave F" wrote:

What do you want to extract from mm/dd/yyyy? The month? Then do =MONTH(A1)

Day: =DAY(A1)

Year: =YEAR(A1)

If I've misunderstood your question, post back.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Heidi" wrote:

I have dates as my row fields.

Problem: I want to group data by week/month/etc., but don't want to graph
the long text that excel spits out (ie 8/29/2005 - 9/4/2005). Instead, I
want to "extract" just the first date of range using LEFT, or some other text
function. This way I can format as date, and then graph (regular chart, not
pivotchart) on a timescale, manipulate data more easily, etc.

However, it appears that no matter how I set the number format in the
original data, or in the field settings of the pivotTable, when I group the
dates the dates come out m/d/yyyy. I don't care what the format, but I want
the dates to have a consistent number of digits(like mm/dd/yyyy) so I can
extract using the LEFT command.

Is there a way to do this? Or am I making this too complicated? Is there
an easier way?

Thank you!
Heidi

PivotTables - formatting Row fields when grouped
 

Another option is to calculate a week starting date in your source data,
and use that field in the pivot table, instead of grouping the dates.
For example, with dates in column A of the source data:

=A2-WEEKDAY(A2,3)



Heidi wrote:
I have dates as my row fields.

Problem: I want to group data by week/month/etc., but don't want to graph
the long text that excel spits out (ie 8/29/2005 - 9/4/2005). Instead, I
want to "extract" just the first date of range using LEFT, or some other text
function. This way I can format as date, and then graph (regular chart, not
pivotchart) on a timescale, manipulate data more easily, etc.

However, it appears that no matter how I set the number format in the
original data, or in the field settings of the pivotTable, when I group the
dates the dates come out m/d/yyyy. I don't care what the format, but I want
the dates to have a consistent number of digits(like mm/dd/yyyy) so I can
extract using the LEFT command.

Is there a way to do this? Or am I making this too complicated? Is there
an easier way?

Thank you!
Heidi


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

PivotTables - formatting Row fields when grouped
 

Roger - thank you so much! I had no idea Windows was the culprit - was
thinking it was Excel.

Thank you!

Heidi

"Roger Govier" wrote:

Hi Heidi

When I group by Week, Excel outputs in the same setting as for my
Regional settings for the short format of Date, which here in the UK I
have set as dd/mm/yyyy.

I am able to use =LEFT(F5,10) to extract the first date from
08/01/2007 - 14/01/2007

Check your Regional settings through StartControl PanelRegional
settings

--
Regards

Roger Govier


"Heidi" wrote in message
...
I have dates as my row fields.

Problem: I want to group data by week/month/etc., but don't want to
graph
the long text that excel spits out (ie 8/29/2005 - 9/4/2005).
Instead, I
want to "extract" just the first date of range using LEFT, or some
other text
function. This way I can format as date, and then graph (regular
chart, not
pivotchart) on a timescale, manipulate data more easily, etc.

However, it appears that no matter how I set the number format in the
original data, or in the field settings of the pivotTable, when I
group the
dates the dates come out m/d/yyyy. I don't care what the format, but
I want
the dates to have a consistent number of digits(like mm/dd/yyyy) so I
can
extract using the LEFT command.

Is there a way to do this? Or am I making this too complicated? Is
there
an easier way?

Thank you!
Heidi