Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting yyyymm and yyyymmdd to "mmm dd, yyyy" | Excel Discussion (Misc queries) | |||
Excel. decimal convert date | Excel Discussion (Misc queries) | |||
Extracting a Date from a YYYYMM number | Excel Worksheet Functions | |||
How to convert a date into the day name in Excel ? | Excel Discussion (Misc queries) | |||
how do I convert a UK date to a US date in excel? | Excel Discussion (Misc queries) |