+ Reply to Thread
Results 1 to 5 of 5

Problem with formatting "time"

  1. #1
    Registered User
    Join Date
    04-06-2016
    Location
    Oklahoma City, Oklahoma
    MS-Off Ver
    2016
    Posts
    26

    Angry Problem with formatting "time"

    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
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,363

    Re: Problem with formatting "time"

    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?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Problem with formatting "time"

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

    E2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If the latter,

    E2:
    Formula: copy to clipboard
    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.

  4. #4
    Registered User
    Join Date
    04-06-2016
    Location
    Oklahoma City, Oklahoma
    MS-Off Ver
    2016
    Posts
    26

    Re: Problem with formatting "time"

    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

  5. #5
    Registered User
    Join Date
    04-06-2016
    Location
    Oklahoma City, Oklahoma
    MS-Off Ver
    2016
    Posts
    26

    Re: Problem with formatting "time"

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 11
    Last Post: 06-05-2020, 02:08 AM
  2. [SOLVED] Brief formula to convert time values to "shift1", "shift2", "shift3".
    By darekpawel in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 04-11-2019, 07:05 AM
  3. Replies: 1
    Last Post: 06-09-2014, 04:11 AM
  4. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  5. [SOLVED] Assign Categories for Time of Day: "Morning", "Afternoon", "Evening"
    By long_shanks in forum Excel General
    Replies: 3
    Last Post: 06-11-2013, 02:59 AM
  6. Problem using "Cells" in "Range" "400" error
    By johnnywinter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-14-2009, 05:46 PM
  7. "Time" problem: Need function to find time&date - ?hours
    By Powrpak in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-17-2008, 08:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1