+ Reply to Thread
Results 1 to 7 of 7

Time/date format not displaying correctly in function box

  1. #1
    Registered User
    Join Date
    01-15-2015
    Location
    USA
    MS-Off Ver
    2011
    Posts
    5

    Time/date format not displaying correctly in function box

    I've replied to this message with an example.

    I have date and time in a single sell in this custom format: YYYY/MM/DD hh:mm:ss. I would like to sort a column to arrange by earliest to most recent. All dates and times display correctly in the cells. However when I try to sort I notice that half of it sorts differently depending on which person entered the data. When I go to those cells I see that the time and date displays differently in the function box. For some the function box has the date/time like this: 7/15/2014 7:18:00 PM although the cell still looks like this: 2014/07/15 19:18:00. Some of the data has this problem, some does not which I presume is why it displays differently. I've tried just formatting the whole column but that doesn't fix it.

    Thank you in advance for help with this!
    Last edited by seanclawson; 08-02-2020 at 04:01 PM. Reason: attaching sample

  2. #2
    Registered User
    Join Date
    01-15-2015
    Location
    USA
    MS-Off Ver
    2011
    Posts
    5

    Re: Time/date format not displaying correctly in function box

    Here is an example of the problem.
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,740

    Re: Time/date format not displaying correctly in function box

    Only the first two are dates, the others are text, which is why you are getting problems.
    You can clean the data by using text to columns & selecting the YMD setting for date on page 3 of the wizard.
    Although you will need to educate the users to input proper dates.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,334

    Re: Time/date format not displaying correctly in function box

    The 'dates' in the rows where JWB is in column D are actually text.

    You can change them to 'real' dates by selecting column B, going to Data>Text to columns... selecting YMD for Column data format on the 3rd step.
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    01-15-2015
    Location
    USA
    MS-Off Ver
    2011
    Posts
    5

    Re: Time/date format not displaying correctly in function box

    Thank you very much Fluff13 and Norie! You knew exactly what the problem was and it is fixed. Now I just need to go through thousands of rows of data and fix it! Thank you again. You've been tremendously helpful.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    8,740

    Re: Time/date format not displaying correctly in function box

    You're welcome & thanks for the feedback.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Time/date format not displaying correctly in function box

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ 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. [SOLVED] Attendance Filter Function Not Displaying results correctly
    By MarkPr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-30-2017, 09:46 AM
  2. Horizontal date series not displaying correctly
    By stephen195076 in forum Excel General
    Replies: 1
    Last Post: 03-18-2015, 05:30 PM
  3. date is not displaying correctly
    By Deb in forum Excel General
    Replies: 4
    Last Post: 02-08-2014, 07:28 PM
  4. [SOLVED] Data Bars in Conditional Format are not displaying % from 0-100 correctly in the cell
    By maldoncn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-21-2013, 02:17 AM
  5. [SOLVED] Displaying date and time format in Listbox
    By pezalmendra in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-02-2013, 09:26 AM
  6. [SOLVED] Time not displaying correctly in textbox on a userform
    By LJ15332 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-05-2012, 08:30 AM
  7. Replies: 3
    Last Post: 09-17-2007, 04:46 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