Convert Date in Excel from mm/dd/yy to yyyymm

[sandigauthier]

Hello there - i'm trying to convert a date in a cell from mm/dd/yy to
yyyymm.

In cell A1 i have a date: 12/31/07
In cell B1 i put: =A1
Then i 'formatted' B1 to yyyymm using the "custom" format feature.

Problem is, that although B1 "displays" correctly (200712), the data
stored in the cell is still "12/31/07" format.

This is causing problems when i create pivot tables because each date,
again, although displays "200712", shows one record for each date.
ie:

My pivot shows:

Month.......# Widgets
200712 ........ 5
200712 ........ 10
200712 ........ 15

When what i want to see is:
200712 ........ 30

The pivot will not sum all of 200712, because the data is stored with
each unique "day" value.

Could use your assistance!
Thanks!

 

[Niek Otten]

Insert a column with

=TEXT(A1,"yyyymm")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello there - i'm trying to convert a date in a cell from mm/dd/yy to
| yyyymm.
|
| In cell A1 i have a date: 12/31/07
| In cell B1 i put: =A1
| Then i 'formatted' B1 to yyyymm using the "custom" format feature.
|
| Problem is, that although B1 "displays" correctly (200712), the data
| stored in the cell is still "12/31/07" format.
|
| This is causing problems when i create pivot tables because each date,
| again, although displays "200712", shows one record for each date.
| ie:
|
| My pivot shows:
|
| Month.......# Widgets
| 200712 ........ 5
| 200712 ........ 10
| 200712 ........ 15
|
| When what i want to see is:
| 200712 ........ 30
|
| The pivot will not sum all of 200712, because the data is stored with
| each unique "day" value.
|
| Could use your assistance!
| Thanks!

 

[sg]

Insert a column with

=TEXT(A1,"yyyymm")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hello there - i'm trying to convert a date in a cell from mm/dd/yy to
| yyyymm.
|
| In cell A1 i have a date:   12/31/07
| In cell B1 i put:  =A1
| Then i 'formatted' B1 to yyyymm using the "custom" format feature.
|
| Problem is, that although B1 "displays" correctly (200712), the data
| stored in the cell is still "12/31/07" format.
|
| This is causing problems when i create pivot tables because each date,
| again, although displays "200712", shows one record for each date.
| ie:
|
| My pivot shows:
|
| Month.......# Widgets
| 200712  ........ 5
| 200712 ........ 10
| 200712 ........ 15
|
| When what i want to see is:
| 200712 ........ 30
|
| The pivot will not sum all of 200712, because the data is stored with
| each unique "day" value.
|
| Could use your assistance!
| Thanks!

That was tooooo easy! Thank you kindly!

 

[Bernie Deitrick]

Sandi,

The simplest and most robust solution is to use the grouping feature of the Pivot table to group by
month, using "sum" in the summarize by for the fields of interest.

HTH,
Bernie
MS Excel MVP