How do I convert a date&time (yyyy-mm-dd hh:mm:ss) to date only?

I am trying to convert a date & time into a date only so I can get a pivot
table to do a daily summary. Is there a function that can convert? Is there
another way to do a daily summary with a pivot table?

Select the cell(s) you want to format.
On the Format menu, click Cells, and then click the Number tab.
In the Category list, click Date or Time, and then click the format you want to use.
Note If you don’t find what you’re looking for, you can create a custom number format by using format codes for dates and times. (taken from excel help)

You Don't have to convert any of the date-time values, just change the cell formatting to change how they are displayed.

hope it helps.

Wesley,

With the date in A1:

=INT(A1)

formatted for date.

HTH,
Bernie
MS Excel MVP


"Wesley Accellent" <Wesley Accellent@discussions.microsoft.com> wrote in message
news:C2F1BD84-A5AB-4D2E-A027-A27A66A71383@microsoft.com...
>I am trying to convert a date & time into a date only so I can get a pivot
> table to do a daily summary. Is there a function that can convert? Is there
> another way to do a daily summary with a pivot table?

Two ways that I know of...

1) In your data table, set up a new column with the funtion
=Round(A1,0) where A1 is the Date&Time field. The "time" is
represented as everything to the right of the decimal place where the
"date" is to the left. This basically converts all of our Date/Time
fields to midnight.

2) In the pivot table, use the Group and Show Detail to convert the
dates/times to "Days". Right click on the list of Date/Times then
choose Group and Show Detail > Group. Click on Days.

- John

It hurts my feelings that it is that easy, but it works great.

Just what I needed.

Thanks,
Wesley

"Bernie Deitrick" wrote:

> Wesley,
>
> With the date in A1:
>
> =INT(A1)
>
> formatted for date.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Wesley Accellent" <Wesley Accellent@discussions.microsoft.com> wrote in message
> news:C2F1BD84-A5AB-4D2E-A027-A27A66A71383@microsoft.com...
> >I am trying to convert a date & time into a date only so I can get a pivot
> > table to do a daily summary. Is there a function that can convert? Is there
> > another way to do a daily summary with a pivot table?
>
>
>

The round worked great. Thanks.

I tried the group function in the pivot table. However, when I selected the
column title and chose Group it responds "Can not group that section".

Any ideas?



"John Michl" wrote:

> Two ways that I know of...
>
> 1) In your data table, set up a new column with the funtion
> =Round(A1,0) where A1 is the Date&Time field. The "time" is
> represented as everything to the right of the decimal place where the
> "date" is to the left. This basically converts all of our Date/Time
> fields to midnight.
>
> 2) In the pivot table, use the Group and Show Detail to convert the
> dates/times to "Days". Right click on the list of Date/Times then
> choose Group and Show Detail > Group. Click on Days.
>
> - John
>
>

Careful with ROUND - date/times after noon today will round to tomorrow....

HTH,
Bernie
MS Excel MVP


"Wesley Accellent" <WesleyAccellent@discussions.microsoft.com> wrote in message
news:514640AE-D86D-494A-A4FF-8B7D10E7CC41@microsoft.com...
> The round worked great. Thanks.
>
> I tried the group function in the pivot table. However, when I selected the
> column title and chose Group it responds "Can not group that section".
>
> Any ideas?
>
>
>
> "John Michl" wrote:
>
>> Two ways that I know of...
>>
>> 1) In your data table, set up a new column with the funtion
>> =Round(A1,0) where A1 is the Date&Time field. The "time" is
>> represented as everything to the right of the decimal place where the
>> "date" is to the left. This basically converts all of our Date/Time
>> fields to midnight.
>>
>> 2) In the pivot table, use the Group and Show Detail to convert the
>> dates/times to "Days". Right click on the list of Date/Times then
>> choose Group and Show Detail > Group. Click on Days.
>>
>> - John
>>
>>

Hard to say without seeing the pivot. On my pivot I had a column on
the left name DateTime. I right clicked on one of the entries and was
able to Group it. Try clicking on one of the values instead of the
title.