Solved

Excel date convert problem

Posted on 2008-10-07
2
411 Views
Last Modified: 2008-10-07
I have one column in excel 2007 in the format of m/d/yyyy h:mm, but I'm trying to run query to group results to days. That means I want to eliminate time for that column. What should I do ?
Although I can change the format directly to m/d/yyyy, but the query GROUP BY dates will still group results to hours.

Thanks
0
Comment
Question by:CodingCat
2 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22663302
If the query is running in SQL Server...

SELECT CONVERT(datetime, CONVERT(varchar, DateColumn, 102)) AS TheDate, SUM(Something)
FROM SomeTable
GROUP BY CONVERT(datetime, CONVERT(varchar, DateColumn, 102))
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 22664626
Hi,

In excel use the trunc() function

in a3 put
=NOW()

in b3 put
=TRUNC(A3, 0 )

Format the results as appropriate ...

Cheers
  David
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now