Archive of Mr Excel Message Board

Back to Pivot Tables in Excel archive index
Back to archive home

Change existing data from Date & Time to just Date

Posted by Paul Magruder on May 16, 2001 10:30 AM

I have 1500 rows of data that was returned using =Now().The problem is I need the Pivot table to Pivot the data, Just recognizing the "date" and ignoring the time. It currently looks for both, and is only returning 1 data point(because of the time).

Thanks in Advance
Paul

Re: Change existing data from Date & Time to just Date

Posted by Kevin James on May 16, 2001 10:35 AM

Hi Paul,

Two choices:

Change Now() to Today().

If Now() serves other functions (requiring the time portion, use concat.

Kevin

PS: Now watch, some brilliant protege will come with options 3 through 1000.

Re: Change existing data from Date & Time to just Date

Posted by Mark W. on May 16, 2001 10:50 AM

Paul, you could either Group your date/time values
by "Years" and "Days" in the PivotTable or create
another column in your data set labelled 'Date'
that uses Excel's TRUNC() function to extract the
date value from your date/time value. For example,
if your date/time values were in column B, then
you'd use =TRUNC(B2) and Copy down.

Opps

Posted by Kevin James on May 16, 2001 11:01 AM

Opps, Mark's right. I intended to suggest TRUNC, not CONCAT. I don't know why, but I STILL have to stop and think about "right" and "left."

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.