We help IT Professionals succeed at work.

group function changes date format

Medium Priority
604 Views
Last Modified: 2012-05-06
Hello,

I am working with an excel 2007 pivot table.  In my table I have a bunch of dates see below for sample:
Employee/app.name      Date      Administration      Available      SoftBooked      Grand Total
Fake      23-Feb            7.5            7.5
      24-Feb            7.5            7.5
      25-Feb            7.5            7.5
      26-Feb            7.5            7.5
      27-Feb            7.5            7.5
      9-Mar            7.5            7.5
      10-Mar            7.5            7.5
      11-Mar            7.5            7.5
      12-Mar            7.5            7.5
      13-Mar            7.5            7.5
      16-Mar            7.5            7.5
      17-Mar            7.5            7.5
      18-Mar            7.5            7.5
      19-Mar            7.5            7.5
      20-Mar            7.5            7.5
from this view I use the group funtion to group on the dates to show weekly summaries see below:
Employee/app.name      Date      Administration      Available      SoftBooked      Grand Total
Fake      2/23/2009 - 3/1/2009            37.5            37.5
      3/9/2009 - 3/15/2009            37.5            37.5
      3/16/2009 - 3/21/2009            37.5            37.5
Fake Total                  112.5            112.5

Here is where I run into the problem, I would like the formatting of the dates to remain as the original pivot table but for some reason it gets changed and I am unable to chage it back....i would greatly appreciate it if anyone is able to help me out! thanks
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2015

Commented:
Well you can convert your dates into text format and then you can use the same in group function...by doing that..they wontchange....what i meant lets say if you have dates in a1...then use this...
=text(a1,"dd/mm/yy")
Assuimng you have date in a1..this will take care of what you are looking fr...
Saurabh...

Author

Commented:
i tried that doesnt work...
i added another col to my source data and put =text(a1,"dd/mm/yy")
then in my pivot table i tried to group on that col and i get an error saying cannot group
CERTIFIED EXPERT
Top Expert 2015

Commented:
Well see the problem is that if your dates are in dates format and you concatenate them excel doesnt recognises them as dates and you convert them into text format to make excel recognise them as dates which is exactly what i did..try doing a paste special values..and then grouping..but i believe that also wont work..since grouping would work..only where excel can find a logic and in text values it wont be able to find it...
 
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
As far as I know, you have no control over how the grouping is shown in the table. You would need to add an additional column to your data (probably easiest using a lookup table) to provide the date bands in the format you want. You can then add this field instead of the date field to the row area of your pivot table.
Regards,
Rory

Author

Commented:
saurabh726:  i tried the paste speical and that doesnt work either.

i've upload a portion of my data. there are two tabs one with the raw data and the other is the pivot...
in the pivot i have grouped by day (every 7 days)..if you ungroup it will show in the format that I need it to be in...

if anyone is able to help me convert my grouped data to the orginial format it would be greatly appreicated!
Date-Format.xls
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Like this?
Regards,
Rory

Date-Format.xls
CERTIFIED EXPERT
Top Expert 2015
Commented:
Further to rory comment...lets say you want to build a database dates without building a database table like rory did..then you can use this..i have got your date concatenated with each other without using a reference table assuming you want to show it from Mon-Sun
It gets the same result as rory...
Saurabh...

Date-Format...xls

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
thanks, i think it is easier for me you use the soltuion saurabh726 has shown!  
I would of never thought of that myself....
soo is there no way to actually format the pivot table?
CERTIFIED EXPERT
Top Expert 2015

Commented:
Nopes...as i told you the dates once conctanated..Excel doesnt recognises them as dates and there is no way without converting them to text to make excel recognise them as dates..so you have to convert them like i did...then only it will work...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.