Solved

Excel date convert problem

Posted on 2008-10-07
2
410 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

896 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

17 Experts available now in Live!

Get 1:1 Help Now