• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

group function changes date format

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
0
jagger04
Asked:
jagger04
  • 4
  • 3
  • 2
1 Solution
 
Saurabh Singh TeotiaCommented:
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...
0
 
jagger04Author 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
0
 
Saurabh Singh TeotiaCommented:
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...
 
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rory ArchibaldCommented:
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
0
 
jagger04Author 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
0
 
Rory ArchibaldCommented:
Like this?
Regards,
Rory

Date-Format.xls
0
 
Saurabh Singh TeotiaCommented:
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
0
 
jagger04Author 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?
0
 
Saurabh Singh TeotiaCommented:
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...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now