Hello,
In Excel 2010... Working with a dataset of 200K+ records and I can't
seem to format a date/time stamp (5/10/2011 9:10:45 AM) correctly so
that a pivot table will summarize by just the date and NOT by date and
time.

TFTH,
Tom

"Tom Taetsch" <(E-Mail Removed)> wrote in message
news:368a6a6f-3e5f-496d-bee3-(E-Mail Removed)...
> Hello,
> In Excel 2010... Working with a dataset of 200K+ records and I can't
> seem to format a date/time stamp (5/10/2011 9:10:45 AM) correctly so
> that a pivot table will summarize by just the date and NOT by date and
> time.
>
> TFTH,
> Tom


Others will be more familiar than I ... but doesn't the pivot table
date/time options allow you to specify what part of the value you wish
to key off of?

My understanding is that the pivot table controls are working off the
underlying date/time VALUE, not the formatting that is presented in the
UI.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Tom Taetsch presented the following explanation :
> Hello,
> In Excel 2010... Working with a dataset of 200K+ records and I can't
> seem to format a date/time stamp (5/10/2011 9:10:45 AM) correctly so
> that a pivot table will summarize by just the date and NOT by date and
> time.
>
> TFTH,
> Tom

Type this in the Immediate Window and see if it returns what you
want...

?DateValue("5/10/2011 9:10:45 AM")

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Aug 30, 11:15*am, GS <g...@somewhere.net> wrote:
> Tom Taetsch presented the following explanation :
>
> > Hello,
> > In Excel 2010... Working with a dataset of 200K+ records and I can't
> > seem to format a date/time stamp (5/10/2011 *9:10:45 AM) correctly so
> > that a pivot table will summarize by just the date and NOT by date and
> > time.
>
> > TFTH,
> > Tom
>
> Type this in the Immediate Window and see if it returns what you
> want...
>
> * ?DateValue("5/10/2011 *9:10:45 AM")
>
> --
> Garry
>
> Free usenet access athttp://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,
Your suggestion works, but I not in this application. Currently, I
have a cell (B4) that is custom formatted as (Date - 3/14) yet the
data that is included in that cell is: 5/10/2011 9:10:45 AM. I tried
your suggestion and it worked only when I typed "5/10/2011 9:10:45
AM" in the formula, but if I used DATEVALUE(B4) it returned #Value!.

TFTH,
Tom

Tom Taetsch formulated on Tuesday :
> On Aug 30, 11:15*am, GS <g...@somewhere.net> wrote:
>> Tom Taetsch presented the following explanation :
>>
>>> Hello,
>>> In Excel 2010... Working with a dataset of 200K+ records and I can't
>>> seem to format a date/time stamp (5/10/2011 *9:10:45 AM) correctly so
>>> that a pivot table will summarize by just the date and NOT by date and
>>> time.
>>
>>> TFTH,
>>> Tom
>>
>> Type this in the Immediate Window and see if it returns what you
>> want...
>>
>> * ?DateValue("5/10/2011 *9:10:45 AM")
>>
>> --
>> Garry
>>
>> Free usenet access athttp://www.eternal-september.org
>> ClassicVB Users Regroup! comp.lang.basic.visual.misc
>
> Garry,
> Your suggestion works, but I not in this application. Currently, I
> have a cell (B4) that is custom formatted as (Date - 3/14) yet the
> data that is included in that cell is: 5/10/2011 9:10:45 AM. I tried
> your suggestion and it worked only when I typed "5/10/2011 9:10:45
> AM" in the formula, but if I used DATEVALUE(B4) it returned #Value!.
>
> TFTH,
> Tom

Tom,
That was VBA I posted, NOT a worksheet function! To extract the date
only using worksheet formula...

=DATE(YEAR(B4),MONTH(B4),DAY(B4))

To extract the time only using a worksheet formula...

=TIME(HOUR(B4),MINUTE(B4),SECOND(B4))


**
Did you read Clif's reply about being able to specify which part of the
date/time stamp to use in your PivotTable? I don't work with
PivotTables much and so I'm afraid I won't be much help on that
subject.<g>

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I would check to make sure each of the entries is really a date/time.

You could use something like:

=count(a:a)
and
=counta(a:a)

=count() will return the quantity of numbers (including dates/times) and
=counta() will return the quantity of non-empty cells.

These should match (if you're careful with the ranges) or they should be the
same after you discount headers.

You could also try using another column and put in a formula to help:

=isnumber(a2)

Then autofilter to look for FALSE values.

Or you could use a non-ambiguous number format (mmm dd, yyyy hh:mm:ss) and
scroll up/down looking for values that did not react to this format change.

If your data is all text, you could use data|text to columns to try to fix it.
If your data is mixed (dates and text), be very careful when you try to fix it.


On 08/30/2011 12:59, Tom Taetsch wrote:
> Hello,
> In Excel 2010... Working with a dataset of 200K+ records and I can't
> seem to format a date/time stamp (5/10/2011 9:10:45 AM) correctly so
> that a pivot table will summarize by just the date and NOT by date and
> time.
>
> TFTH,
> Tom

--
Dave Peterson