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