We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
10 days, 18 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Filtering dates and times in excel 2010 and 2013.
Posted on 2013-01-01
I am working with exported data from a flat database, I have no control over its initial formatting.
When the data is exported its comes out with a date formatted as “dd/mm/yyyy hh:mm:ss”.
I need to sort, filter and makes reports with date and time.
What is the best way for doing this?
I am currently using “Text to Columns” and splitting the date and time into two columns.
I am then able to format the date as “date”, and I can sort on that column, and I can use filter to filter dates.
When I format time as time or dd:mm:yyyy it lets me sort on the column, but I can’t get a filter time. What am I doing wrong?
When the data is exported its comes out with a date formatted as “dd/mm/yyyy hh:mm:ss”.
I need to sort, filter and makes reports with date and time.
What is the best way for doing this?
I am currently using “Text to Columns” and splitting the date and time into two columns.
I am then able to format the date as “date”, and I can sort on that column, and I can use filter to filter dates.
When I format time as time or dd:mm:yyyy it lets me sort on the column, but I can’t get a filter time. What am I doing wrong?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
9
5
4
18 Comments
Hi Joseph it seems like this should work OK, can you send a sample sheet with the problem data? Is it possible the time column is being treated as text or was not converted on the text to columns process?
...Terry
...Terry
Hi, Joseph_Hall.
I can’t get a filter time.
Do you mean that you can't get the hierarchical filter (where it shows hours with a plus symbol beside to expand to minutes, etc.)? These are only shown for times when they are part of a date.
Regards,
Brian.
I can’t get a filter time.
Do you mean that you can't get the hierarchical filter (where it shows hours with a plus symbol beside to expand to minutes, etc.)? These are only shown for times when they are part of a date.
Regards,
Brian.
Hi Brian.
Drat .. I thought I sent this in the morning . dont now why it did not go.
I am a lil confused.
if you look at the uploaded spreadsheet you will see two worksheets
the first week sheet is in form of one collumn as "dd/mm/yyyy hh:mm:ss". This is how the data is exported from Gemini.
I am no table to use the date format on that string.
To ge it working I have to split the date and the time.
So if you look in the second work sheet you see two comumns being used. in this case I am able to fomat the date cumomn as DATES, and use fate filters. But the time column when formated as time only gives me texts filters.
Regs
Joe
Drat .. I thought I sent this in the morning . dont now why it did not go.
I am a lil confused.
if you look at the uploaded spreadsheet you will see two worksheets
the first week sheet is in form of one collumn as "dd/mm/yyyy hh:mm:ss". This is how the data is exported from Gemini.
I am no table to use the date format on that string.
To ge it working I have to split the date and the time.
So if you look in the second work sheet you see two comumns being used. in this case I am able to fomat the date cumomn as DATES, and use fate filters. But the time column when formated as time only gives me texts filters.
Regs
Joe
Sorry here are the example dates
So if you look in the second work sheet you see two comumns being used. in this case I am able to fomat the date cumomn as DATES, and use fate filters. But the time column when formated as time only gives me texts filters.
Gemini-Sample.xlsx
So if you look in the second work sheet you see two comumns being used. in this case I am able to fomat the date cumomn as DATES, and use fate filters. But the time column when formated as time only gives me texts filters.
Gemini-Sample.xlsx
I think I understand... On the Gemini Original sheet, multiply all the values by 1, which will convert them from text to date, then they will format and group OK in your autofilter.
Oh, to do that, type 1 into the Gemini Original sheet, in say B2, Copy it, then select all of the values under Date/Time column, then Paste Special > Values > Multiply > OK
Then format the dates as required, autofilter it and delete the 1 in B2.
If you don't want the dates grouped in AutoFilter, go to File > Options > Advanced > Display options for this workbook > uncheck Group dates in the AutoFilter menu
Then format the dates as required, autofilter it and delete the 1 in B2.
If you don't want the dates grouped in AutoFilter, go to File > Options > Advanced > Display options for this workbook > uncheck Group dates in the AutoFilter menu
at he risk of being dumb .. what do mean multiply all the values by 1?
In cell a2 you have "30/12/2012 08:22:46".
if in cell c3 I put "=c2*1" I get 41273.349. how dose this help?
Joe
In cell a2 you have "30/12/2012 08:22:46".
if in cell c3 I put "=c2*1" I get 41273.349. how dose this help?
Joe
ok lets me expain this again ..
I have a data / time field on one cell . .ie "30/12/2012 08:22:46"
for this to be usable I want to be able to sort and filter on these values, how do I do that?
I assume I have to spit the two vaues?
I have a data / time field on one cell . .ie "30/12/2012 08:22:46"
for this to be usable I want to be able to sort and filter on these values, how do I do that?
I assume I have to spit the two vaues?
Just have a look at my last post Joe, can you do what I said there and let me know? I sent two posts one after the other, the second had the how-to, sorry about that
Joe,
The following will convert these text values to "proper" Date+Time format...
(1) In "Gemini Original" select the range A2:A187.
(2) On the Ribbon's Menu Bar, select Data and then "Text to Columns".
(3) Select the "Delimited" radio button (its probably already selected).
(4) Click on "Next".
(5) Only the Tab delimiter checkbox should be selected.
(6) Click on "Finish".
Edit: Alternatively you could use this formula...
=DATEVALUE(MID(A2,1,8))+TI
(As an aside, the column is formatted align-left.. This makes absolutely no difference to your issue but it does make it harder to spot that a cell is not, contrary to appearance, a numeric value.)
Regards,
Brian.
The following will convert these text values to "proper" Date+Time format...
(1) In "Gemini Original" select the range A2:A187.
(2) On the Ribbon's Menu Bar, select Data and then "Text to Columns".
(3) Select the "Delimited" radio button (its probably already selected).
(4) Click on "Next".
(5) Only the Tab delimiter checkbox should be selected.
(6) Click on "Finish".
Edit: Alternatively you could use this formula...
=DATEVALUE(MID(A2,1,8))+TI
(As an aside, the column is formatted align-left.. This makes absolutely no difference to your issue but it does make it harder to spot that a cell is not, contrary to appearance, a numeric value.)
Regards,
Brian.
OK thanks .. got that to work .. sort of.
doing what you say I can get a single field ""30/12/2012 08:22:46" to work.
But when I try to split into two ..
A2 = 30/12/2012
B2 = 08:22:46
I format A as date
I format B as time
and aply filter ... then A as date filter, but be has number filter.
Number filter kinda seams to work as time filter .. but not sure. Can you confirm if that is true?
Joe
doing what you say I can get a single field ""30/12/2012 08:22:46" to work.
But when I try to split into two ..
A2 = 30/12/2012
B2 = 08:22:46
I format A as date
I format B as time
and aply filter ... then A as date filter, but be has number filter.
Number filter kinda seams to work as time filter .. but not sure. Can you confirm if that is true?
Joe
Joe,
Edit: Oops, the following is a crossing post.
As I mentioned in my initial post, Excel only does a hierarchical filter for Time as a part of a Date, so, as you've seen, a time appears as a numeric filter.
However, you can still enter Times into the filter, e.g. 08:00:00. (Of course, if you look at the filter later, your nice time will now show as 0.333333333333333 - which is actually how Excel stores Times internally.)
Regards,
Brian.
Edit: Oops, the following is a crossing post.
As I mentioned in my initial post, Excel only does a hierarchical filter for Time as a part of a Date, so, as you've seen, a time appears as a numeric filter.
However, you can still enter Times into the filter, e.g. 08:00:00. (Of course, if you look at the filter later, your nice time will now show as 0.333333333333333 - which is actually how Excel stores Times internally.)
Regards,
Brian.
OK .. just double checked with the live data .. I use the "Text to Columns" option, but not realy spliting it... t works, and is much easia than I was trying to do by spliting the data.
With out even reformting the date / time field the filter auto pics it up correctly.
thanks.
Joe
With out even reformting the date / time field the filter auto pics it up correctly.
thanks.
Joe
Featured Post
August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
A little background as to how I came to I design this code:
Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses
Course of the Month10 days, 18 hours left to enroll
770 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.