Convert Date in month?

[shital]

i have filed like date,bill, name,qty,rate,amount, MONTH.
where i have data more than 200 every month.
What i want is i have date in format dd/mm/yy now is there
any way when there is a month of april all date of april
should be display as a april in MONTH filed, may should be
display as a may and so on.

so that i can use all my data for pivot-table.

E.g.

date bill, name,qty,rate,amount MONTH.
01/04/03 123 April
02/04/03 234 April
23/04/03 345 April
02/05/03 237 May
26/05/03 203 May

Thanks in advance.

Shital

 

[Harald Staff]

Hi Shital

=TEXT(A2,"mmmm")
should hopefully do.

 

[Paul]

i have filed like date,bill, name,qty,rate,amount, MONTH.
where i have data more than 200 every month.
What i want is i have date in format dd/mm/yy now is there
any way when there is a month of april all date of april
should be display as a april in MONTH filed, may should be
display as a may and so on.

so that i can use all my data for pivot-table.

E.g.

date bill, name,qty,rate,amount MONTH.
01/04/03 123 April
02/04/03 234 April
23/04/03 345 April
02/05/03 237 May
26/05/03 203 May

Thanks in advance.

Shital

For a date in A1,
=TEXT(A1,"mmmm")
will give the month as a text string.
(e.g. 01/04/03 will give "April".)

 

[Jim Rech]

If you want a date cell to display as its month name only, give it the
number format "mmmm" (no quotes)>

 

[Paul]

If you want a date cell to display as its month name only, give it the
number format "mmmm" (no quotes)>

But formatting thus will not help OP, as he said he wanted to use for a
pivot table.

 

[Ron Rosenfeld]

i have filed like date,bill, name,qty,rate,amount, MONTH.
where i have data more than 200 every month.
What i want is i have date in format dd/mm/yy now is there
any way when there is a month of april all date of april
should be display as a april in MONTH filed, may should be
display as a may and so on.

so that i can use all my data for pivot-table.

E.g.

date bill, name,qty,rate,amount MONTH.
01/04/03 123 April
02/04/03 234 April
23/04/03 345 April
02/05/03 237 May
26/05/03 203 May

Thanks in advance.

Shital

=date with a custom format of "mmmm" without quotes will store the date and
display the MONTH spelled out.

=TEXT(date,"mmmm") will give the MONTH spelled out as text.




--ron

 

[Debra Dalgleish]

Instead of using a column in the source table, you can group the dates
in the Pivot Table.

1. Add the date to the row area of the pivot table
2. Right-click on the Date field button
3. Choose Group and Show Detail>Group
4. Select Months, or Years and Months, click OK

 

[Maureen O'Gorman]

use this formula

=TEXT({cell address},"MMMM")

 

[Brian Murphy]

Shital,

If you already have the data input in the "Date" column I would do the
following:

1) Highlight all the dates in the "Date" column and copy them.
2) Paste these cells into the "MONTH" column.
3) Select the "Month" column and go to Format --> Cells on the top
toolbar.
4) In the category box select "Custom"
5) In the "Type:" box type mmmmmmmmm and click OK.

Note: It's up to you how many m's you want to type if you type 3 it
will display the 1st three letters of the month. If you type 9 m's
that will cover the longest month name of September so it will display
the full month name no matter which month it is.

 

[Debra Dalgleish]

Instead of using a column in the source table, you can group the dates
in the Pivot Table.

1. Add the date to the row area of the pivot table
2. Right-click on the Date field button
3. Choose Group and Show Detail>Group
4. Select Months, or Years and Months, click OK

 

[Ron Rosenfeld]

Shital,

If you already have the data input in the "Date" column I would do the
following:

1) Highlight all the dates in the "Date" column and copy them.
2) Paste these cells into the "MONTH" column.
3) Select the "Month" column and go to Format --> Cells on the top
toolbar.
4) In the category box select "Custom"
5) In the "Type:" box type mmmmmmmmm and click OK.

Note: It's up to you how many m's you want to type if you type 3 it
will display the 1st three letters of the month. If you type 9 m's
that will cover the longest month name of September so it will display
the full month name no matter which month it is.

Four m's will display the full month name with US regional settings.

Do international settings work differently with this formatting?


--ron

 

[David Turner]

Brian Murphy wrote

If you type 9 m's
that will cover the longest month name of September so it will display
the full month name no matter which month it is.

Actually 4 m's will cover any length month name.

 

[Dave Peterson]

Another option is to not add that extra column.

Use the date column in your pivottable, but then Group the dates by month.

In the PT, right click on a date and choose "Group and Show Detail", then
"group".

And group by month (although I think I'd include the year, too.)

 

[Arvi Laanemets]

Hi

Four m's will display the full month name with US regional settings.

Do international settings work differently with this formatting?

More exactly, the format "mmmm" displays full month name ("mmm" displays
short month name) accordingly computers International settings. I.e. when
such cell displays "January" in your computer, then opening the same
workbook in my computer, I'll see "jaanuar" there.

 

[Ron Rosenfeld]

More exactly, the format "mmmm" displays full month name ("mmm" displays
short month name) accordingly computers International settings. I.e. when
such cell displays "January" in your computer, then opening the same
workbook in my computer, I'll see "jaanuar" there.

More clearly, my question should have been with regard to whether the 4 m's
display something other than the full month name using other versions or
regional settings of Excel. I would expect that the month would be translated
into the appropriate language.




--ron