Formats and Pivots

[Don]

Hi all,

I have a worksheet of data, one column of which is date (dd-mmm-yyyy). For
Pivot table purposes I want to extract mmm and yyyy into seperate columns. I
have no problem with this. However, if I included these columns in a pivot
table I have multiple record entries for the same mmm and yyyy. Scrolling
over each of them it appears they are unique as for some reason a 24Hr time
stamp is included : (

Any ideas how to resolve this?

Don-

 

[bj]

use a helper column and enter =integer(date column)
will get rid of the time stamp.

 

[Peo Sjoblom]

If you have different time stamps in the same cell as the dates then they
are not unique. one hour is 1/24 and one day is 1, a date is simply the
number of days since Jan 0 1900 meaning that today at 00:00 is 39199 and
today at noon is 39199.50 so these 2 are not the same. Are you trying to
group the dates in the pivot table, that way you can get months and years in
separate columns?

Otherwise you can use

=INT(A2)

will convert dates to the particular date at 00:00