Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill default: I want Fill Without Formatting | Excel Discussion (Misc queries) | |||
expanding custom formatting without removing existing cell formatting? | Excel Worksheet Functions | |||
PivotTables headings formatting ? | Excel Discussion (Misc queries) | |||
Sorting Spreadsheet with Merged Fields | Excel Discussion (Misc queries) | |||
Formatting date fields after export | Excel Discussion (Misc queries) |