Time Filter Problem in Excel 2013

[Caz59]

Hi there,
I have created a table in Excel, date, meeting date start time, meeting date finish time, columns are formatted with Date and Time using format cells. The date field is fine but the time field even though is telling me is filtered by time when I use the sort it is not. When I use Sort I get the error that some cells contain some numbers formatted as text but for the life of me cannot find where the error is! Help!

 

[combo]

If you have numbers formatted as text, just change their format.
You may have problem with numbers/dates/time STORED as text, if it is not intentional, they should be converted. The simplest way of conversion is to add 0 or multiply by 1 via paste special action.
To find cells that store text convert format of cells with time to general. For regular time (and date) values you should see numbers, text will stay text.

combo

 

[Caz59]

I tried everything and Excel STILL telling me some time formats were text. So, perhaps this the long way round but I sorted the Time columns, the ones at the bottom of the list were obviously the ones that "thought" they were text (even though in Format cells it still stated they were Time formatted). I then copied the "good" times to the "bad" cells and bingo it works. Doesn't quite make a lot of sense as I started a completely new worksheet, formatted my columns and started typing so don't quite get how this happens! Thanks for reply!

 

[combo]

I tried everything and Excel STILL telling me some time formats were text.

There is a difference between format and contents of the cell in excel (there is also cell's text, that is what excel displays), time format cannot be text. As I wrote above, you can change format of the cell to whatever, no problem.
I guess that you have some cells that excel treats as text, even if the contents looks like time. The reason for this may be differnt, among others (+solution):
1. the cell was formatted as text and you entered date/time/number (format properly, next re-enter contents oof the cell, for big blocks of data copy empty cell, paste special values with adding operation),
2. cell contents starts with space " " or text marker "'" (find and replace character by no text),
3. date, time or number is not valid, i.e. invalid month or day in given month, decimal separator (use proper data).

combo

 

[Gavona]

By the way, If you have dates that are't recognised as dates a reliable way of converting is to select the column, Data >> Text to Columns, choose delimited and specify a delimiter that is not in your data and then specify the column to be date. I don't think this works with times.

Gavin