Problem with formatting "time"

[Diane Hogue]

I have a spreadsheet that I received from someone else. It has a date column and a "Procedure Start Time*" column that is displayed as hh:mm:ss.000. Ultimately, I want to concatenate the date and time into a single column, formatted as mm/dd/yyyy hh:mm:ss AND be able to filter the new, combined column by group (ordinarily displayed as a "tree").

No matter what I do to the "Procedure Start Time*" column, once it is combined with the date, I am not able to filter by group.

Here are the things I've tried so far:

1) I've formatted Column B as "hh:mm:ss.000"
2) I've repeatedly tried data text to columns, formatting each piece separately and then re-combining them by using the TIME formula
3) I've tried cleaning and trimming
4) I've copied pasted "values only"
5) I've tried converting the time to a decimal number and tried to re-convert it back to a number
6) I've copied to an entirely new sheet
7) I've tried numerous different TIME and TIMEVALUE formulas

Although I can get the new, "combined" column to "look" right, I cannot get it to filter correctly. Based on all of my research today, it has something to do with the time column. The date piece seems to be fine.

I would welcome ANY suggestions. I am an Intermediate to Intermediate Advanced user, not an Expert.

Thanks in advance!

Diane

[MrShorty]

If I understand your question, the problem comes from having your dates (in column D) and your date + time (in column F) as text strings rather than real date/time serial numbers. The fix is to get date/times as numbers and not as text strings:

1) I nested the formula in column D inside of a DATEVALUE() function so that this column will be a real date =DATEVALUE(current formula).
2) I replaced the formula in column F with a simple addition =D2+B2.

With those two changes, column F shows the same filtering options as column E.

Is that what you are looking for?

[hrlngrv]

Do you want the column E values to be text or date-time numeric values? If the former,

E2:

Please Login or Register  to view this content.

If the latter,

E2:

Please Login or Register  to view this content.

and give it the number format mm/dd/yyyy hh:mm:ss

I suspect you need to use date+time numeric values in order to use filtering.

[Diane Hogue]

Ummmm, yes. It is EXACTLY what I wanted and do you know how embarrassed I am now??!! LOL. Thank you so very much for your help!

Diane

[Diane Hogue]

sigh. The latter seems to work perfectly. I can't believe that is the one thing I DIDN'T try. I feel like a goober, now, but really appreciate your response.

Diane