Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

group function changes date format

Posted on 2009-02-18
9
Medium Priority
?
596 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
0
Comment
Question by:jagger04
  • 4
  • 3
  • 2
9 Comments
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 23676131
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
 

Author Comment

by:jagger04
ID: 23676225
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
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 23676289
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 23676807
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
 

Author Comment

by:jagger04
ID: 23677263
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 23678424
Like this?
Regards,
Rory

Date-Format.xls
0
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 2000 total points
ID: 23678695
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
 

Author Comment

by:jagger04
ID: 23683438
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
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 23683482
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question