Group date fields by month n pivot table 2

[searlerm]

Hi

I have a pivot table with invoices by date & product quantities on each invoice. The raw data comes from Access using date/time a fields. I have grouped the invoices into days, months & then years using the group facility but this now prevents me from formatting the invoices dates as dd/mm/yyyy - it forces it to stay in dd-mmm.

Any suggestions please?

Rhanks

Richard

 

[PJFry]

The first thing I would do is check the formating on that column. Make sure that it is set to format the way you want it.

PJ

 

[SkipVought]

Richard,

Duplicate the date in an adjacent column by reference.

Check the box in the querytable Data Range Parameters for copying adjacent formulas down.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[searlerm]

Hi Guys - thanks for your replies. Unfortunately, neither of this seems to work for me. I have tried formatting the column, Field Settings/Number & even the cells but it keeps it as dd-mmm. However, when sorting on this field, it looks like it's treating this as text - e.g. 01-Dec, 01-Feb, 02-Dec, 02-Feb & so on.

When I referenced an extra column to the invoice date line outside of the pivot table it does the same thing!

I'm sure i must be missing something basic here!

Thanks again

Richard

 

[SkipVought]

Ohhhhhhhh, is your query result a pivot table?

Can you change your query to return NOT a pivot table and then do the pivot on this data?

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[searlerm]

Hi Skip - yes it is as this appeared to get around the problem of having to set the source data range to exclude blanks. I have now set the query to return the data to a pivot table & to generate a pivot from there but it comes up with the same problems (plus the need to reset the source data range each refresh as Group on date doesn't like blanks)

Aarrrgh - I feel like I'm going round in circles!!

Thanks for your help

Richard

 

[SkipVought]

Set you calculated date field in Excel
[tt]
=if(isblank(TheDate),0,TheDate)
[/tt]
then Group on the NEW date (there will be no blanks) and exclude the bogus dates.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[pbrodsky]

Or add your own calculated day, month and year fields to the query, and use those in the pivot table rather than using the pivot table's "Grouping

 

[searlerm]

Thanks guys

I have actually now done a combination of month/date fields added to the query for one pivot table & grouping using Skip's formula for the others

Thanks again for your help

Richard