Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Wesley Accellent
 
Posts: n/a
Default 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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
goober
 
Posts: n/a
Default How do I convert a date&time (yyyy-mm-dd hh:mm:ss) to date only?


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.


--
goober
------------------------------------------------------------------------
goober's Profile: http://www.excelforum.com/member.php...o&userid=19838
View this thread: http://www.excelforum.com/showthread...hreadid=489790

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default How do I convert a date&time (yyyy-mm-dd hh:mm:ss) to date only?

Wesley,

With the date in A1:

=INT(A1)

formatted for date.

HTH,
Bernie
MS Excel MVP


"Wesley Accellent" <Wesley wrote in message
...
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?



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John Michl
 
Posts: n/a
Default How do I convert a date&time (yyyy-mm-dd hh:mm:ss) to date only?

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Wesley Accellent
 
Posts: n/a
Default How do I convert a date&time (yyyy-mm-dd hh:mm:ss) to date onl

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 wrote in message
...
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?






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Wesley Accellent
 
Posts: n/a
Default How do I convert a date&time (yyyy-mm-dd hh:mm:ss) to date onl

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


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default How do I convert a date&time (yyyy-mm-dd hh:mm:ss) to date onl

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

HTH,
Bernie
MS Excel MVP


"Wesley Accellent" wrote in message
...
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I create a schedule from a list of dates ? Gavin Morris Charts and Charting in Excel 2 October 28th 10 04:08 PM
How do I convert a list of date of births into age in Excel? Frieda Excel Worksheet Functions 6 March 29th 07 11:32 PM
How do I convert Date serial number to date rdunne Excel Worksheet Functions 1 April 12th 05 03:04 PM
convert julian date to gregorian date ammaravi Excel Discussion (Misc queries) 1 December 14th 04 09:17 PM
How do I convert a Julian date into a regular date? Jessica Excel Discussion (Misc queries) 4 December 2nd 04 03:54 AM


All times are GMT +1. The time now is 04:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"