Date Time Stamp formatting

[Tom Taetsch]

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

 

[Clif McIrvin]

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.

 

[GS]

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")

 

[Tom Taetsch]

Tom Taetsch presented the following explanation :



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

 

[GS]

Tom Taetsch formulated on Tuesday :

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>

 

[Dave Peterson]

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.