PDA

View Full Version : Date Format issues



coserria
03-27-2008, 05:22 AM
Ok here it is, I have dates coming for a database server based in Europe. Date is formatted with a time stamp

Ex 23/07/2007 23:45:21

I need this date stamp, formatted down to just a month/year like so

Ex JUL/07

I have tried here on this forum, I have read all the help files I think are relevant.

I can get the date formatted as it should be seen yet I'm trying to perform an analysis of an inventory based on usage by month and when i try to use it in a Pivot table it comes back as day, month, year

Ex 23/JUL/07 or any other variant the you can format a date into.

so when I convert it to text in a new column I get it correctly and looks great. good series of codes.

the only problem is that it is not organized alphabetically. Sucks

am I asking for too much? Can it be done.

Bob Phillips
03-27-2008, 06:08 AM
Use the group option in PT.

Build the PT, the select the date heading, right-click, select Group And Show Detail>Group>Months.

coserria
03-27-2008, 06:30 AM
While i'm reworking my set up for this series. are you saying to build it with the date as DD/MM/YY, oor as text where I have MMM/YY? I will end up tryign both ways i guess better to learn that way anyway.

Thanks

Bob Phillips
03-27-2008, 06:40 AM
No, I am saying keep it as true dates in the data, format as you wish, but group in the PT.

coserria
03-27-2008, 06:57 AM
XLD,

PT will not group by that selection.

Here is a bit more info, I could have an action that takes place every day and I might have to look a more then one year. I only need to look as close as on month to my data, so the 256 columns is not enough. I could try to use access but we don't have that program. I am not a pirate so I?m not going to get a hot license. The Text date works, as I will be cutting the pivot table to another worksheet to get just the values. Then can make more graphs the data there.

I think I'm pretty much out of luck on Excel. Hmmm think we found something that it can't do. better be quite Microsoft might hear us.

Thanks

Colorado Moutian

Bob Phillips
03-27-2008, 07:15 AM
Why not post the workbook and let's see.

coserria
03-27-2008, 10:25 AM
ok so here it is all prepared. I have attached a it the way I was setting it up.

I was having problems with getting the macros to run right after each other. the date column is not acting funny but i was using column b for the pivot anyway.

OK so I think I?m all screwed up now. It is not giving me the dates as they are originally set in the data.

I have attached all of the marcos that I have been using and I'm sure that they are in need to seem looking at. the pivot table is month, qty, description and MRN.

Any help would be great.

I am in deeper now then before. De bugging

Bob Phillips
03-27-2008, 10:30 AM
Wherer is the PT?

Trevor
03-27-2008, 11:28 AM
off subject XLD, I like your tag :beerchug: and yet so true

coserria
03-27-2008, 11:31 AM
: pray2: Ok I had to rebuild the worksheet. Please take a look at the entire set of subs. I am having trouble getting the pivot table to run now as well.

I am having trouble with the dates, if you run the last macro number four it will set it all up and you will be able to see what I?m talking about.

the first column is giving a month year that is for year 2007, column b is taking that and making it 2008.

the pivot table will work but I can't get the dates converted correctly.

the company that makes the program that is the source it the test sheets 1, 2, and 3. Is a French company. but it is based on a server in London. for US, Europe computers.

Then I?m beginning to realize that the series of macros will only work in the sheet that has the same name. so I'm guessing I?m calling the application incorrectly.

There it is as small as I can make it and still keep the size that it makes and example with. The actual working copy can and will be working with 20,000 plus lines and possibly up to 50,000.

I was working with one that was 102,000 the other day but I have been doing the entire thing manually, then importing into Access to get the data in a single table and exporting back to Excel to get the pivot table. Still had the date MMM/YY in alphabetical order not month order.
:help

Bob Phillips
03-28-2008, 04:19 AM
I get an error

coserria
03-28-2008, 04:44 AM
i tried it after saving to my computer from here, and got error too. I do not know how to put the code in so that it will worked for others I have been using the personal workbook.

I found a way around it but finding out what the pivot table was reading.

with the year as part of the field name it runs alphabetically,

without just month names it runs in month order.

for the work that is being done it has to be done with the year in the header.