Custom Number format

[rlsublime]

is there a way for me to do a custom format in excel that will convert the date from 1/5/2011 to 1/1/2011. so basically I am trying to keep the following format : mm/01/yyyy. I tried entering this as a custom format but it did not work. Thanks

[dilipandey]

HI risublime,

Try using below custom format:-
m/"1"/yyyy

Regards,
DILIPandey

<click on below 'star' if this helps>

[rlsublime]

Thanks that worked, But I realized that I would want it to display it as Jan-12 while still maintaining this rule:m/"1"/yyyy. Is there a way to do this? Thanks

[dilipandey]

Hi rlsublime..

Custom format is for display only.. either m/"1"/yyyy OR mmm-yy

Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[rlsublime]

is there a way to permanantly convert the dates to that format using vba perhaps or any other way? Thanks

[rlsublime]

I am basically trying to add it to a pivot table and sort by month. So if I have the following months, I want the pivot to group it in for eg "Jan-11 instead of having 1/1/2011, 1/2/2011 etc

Here is a sample data month range
07/22/2011
07/25/2011
07/26/2011
07/27/2011
07/28/2011
07/29/2011
08/01/2011
08/02/2011
08/03/2011
08/04/2011
08/05/2011
08/08/2011
08/09/2011
08/10/2011
08/11/2011
08/12/2011
08/15/2011
08/16/2011
08/17/2011
08/18/2011
08/19/2011
08/22/2011

[Marcol]

You could possibly convert your list
1/.

=DATE(YEAR(A2),MONTH(A2),1)

2/.

=TEXT(A2,"mmm")

3/.

=TEXT(A2,"mmm - yy")

Or some other varietion to suit your needs

[rlsublime]

is there any way to do something like this =TEXT(A2,"mm/01/yyyy")

[Marcol]

Try, to return a string

=TEXT(A2,"mm")&"/01/"&TEXT(A2,"yyyy")

Or to return a serial date

=DATEVALUE(TEXT(A2,"mm")&"/01/"&TEXT(A2,"yyyy"))

Check the last one against your Regional Date Variation (USA style)

[shg]

Or =A2-DAY(A2)+1 and format however you wish.

[Marcol]

@ shg
There are a million and one ways to do things .... that's the one I didn't think of ...

[ChemistB]

You should be able to group your pivot table by month assuming that your dates are stored as numbers and not strings.