MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

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.


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

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.

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