Excel date formatting

[matpj]

Hi,

I have a spreadsheet full of data of which one colum is 'Created Date'
this date is in the format
'dd/mm/yy hh:mm:ss AM'

I need to run pivot-tables using this data regularly, but need to group all rows with a date in october, as October-03 etc...
I can change the cell format so that it reads that way, but the formaula bar still shows the full date and timestamp, and the pivot-table groups all entris by the full date, so there will be lots of different October-03's.
does this make sense?

Is it possible to autmoate this task, or make it as painless as possible? I have tried pasting to notepad and then back again. this gets rid of the time section of the date, but for some reason months in different years such as April-02 and April-03 come out as
04/2/03 and 04/03/03 respectively.

I am wasting so much time trying to do this.
Ideally just a function to replace 'october-03' as a text string (not the data code)

any ideas??

thanks,
Matt

 

[xlbo]

whack the pivottable on the data and group it in there - if you try and group on a date, you will get options to group by month / by year etc

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[matpj]

can yuo break that down in step by step terms - as I am new to this.
at the moment, I have my pivot-table on a seperate sheet.
and the selection is around all of the columns in the spreadsheet.

 

[xlbo]

In your pivottable, right click on the date field
Choose Group&Show Detail>Group
using CTRL, select Months & Years from the list
ok it et voila

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[matpj]

xlbo, thanks for that

Just to refresh this post - does anyone know how I can actually change the date before the pivot table so that any date I put in will automatically set itself to the first day of that month?

 

[xlbo]

eh ???
could you explain.......preferably with an example

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[matpj]

sorry!

right here is an example of the data in my cells:

"5/14/2001 1:13:00 PM"

I need to change this so that it is "05/01/2001"

can it be done?

 

[AlsGal52]

Does it HAVE to show the 1st of the month? Couldn't you just change the format so it shows "05/2001". If so, all you'd have to do is go to customize cell and show mm/yyyy.

 

[matpj]

customize cell? or format cell?

if I change the formatting, it still keeps the same value in the background...

 

[xlbo]

ok - you got 2 options here (but I still don't understand WHY you need to do this)

1: Add an extra column and use
=A1-DAY(A1)+1
where your date is in A1
2: Use the worksheet change event and some code like

if target.column <> 1 then exit sub

target.value = target.value - worksheetfunction.Day(target.value)+1

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244