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

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

Post by V2VzbGV5IE » Fri, 02 Dec 2005 23:12:03


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?
 
 
 

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

Post by goobe » Fri, 02 Dec 2005 23:33:05


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 yo
want to use.
Note If you don find what youe looking for, you can create
custom number format by using format codes for dates and times. (take
from excel help)

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

hope it helps

 
 
 

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

Post by Bernie Dei » Fri, 02 Dec 2005 23:35:29

Wesley,

With the date in A1:

=INT(A1)

formatted for date.

HTH,
Bernie
MS Excel MVP
 
 
 

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

Post by John Mich » Fri, 02 Dec 2005 23:38:18

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