Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel date convert problem

Posted on 2008-10-07
2
Medium Priority
?
420 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 93

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 2000 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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

926 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