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

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!

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

Insert a column with

=TEXT(A1,"yyyymm")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ...
| 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!

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

On Jan 7, 1:45*pm, "Niek Otten" wrote:
Insert a column with

=TEXT(A1,"yyyymm")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in ...

| 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!

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

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


wrote in message
...
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!