+ Reply to Thread
Results 1 to 10 of 10

Different Date Formats In Same Column

  1. #1
    Registered User
    Join Date
    11-14-2017
    Location
    Netherlands
    MS-Off Ver
    Office 2013 ProPlus
    Posts
    5

    Question Different Date Formats In Same Column

    Dear All,

    Currently I am creating a report from data coming from our SharePoint server.
    What Im trying to do is to create a timeline that shows mutations per set time of date.
    The only issue that I'm having, is that the date formats are different in the same column. Some dates are shown as d/mm/yyyy while some are d/mm/yy.
    Changing the format by going to "format cells" does not seem to work for the dates that are shown as d/mm/yyyy.

    Is there any possible way to change all date formats to the same? I'm really not looking forward to manually locate and changing the date formats in a sheet that has over 250k rows..

    Please let me know if possible, that would be amazing!

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Different Date Formats In Same Column

    Hello and welcome to the forum.

    I'm guessing that the "dates" that are formatted as d/mm/yyyy are being seen as text and not an actual date. You can test this with ISNUMBER.
    (e.g. =ISNUMBER(A1) will return FALSE if the value in A1 is being seen as text.

    Try highlighting the column > Data > Text to Columns > Next > Next > Date: DMY > Finish.

  3. #3
    Registered User
    Join Date
    11-14-2017
    Location
    Netherlands
    MS-Off Ver
    Office 2013 ProPlus
    Posts
    5

    Re: Different Date Formats In Same Column

    The problem I also am having is that the date format is also different. Example: Some are dd/mm/yyyy and some mm/dd/yyyy.
    Trying to change this with format cells but no changes are being made.

    The formula = FALSE. Even after changing the column its still FALSE.

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Different Date Formats In Same Column

    That changes the situation. How can you tell if (for example) 04/11/2017 is April 11th or November 4th?

  5. #5
    Registered User
    Join Date
    11-14-2017
    Location
    Netherlands
    MS-Off Ver
    Office 2013 ProPlus
    Posts
    5

    Re: Different Date Formats In Same Column

    Because for some dates, the "day" does not go above 12 and for the other dates the "month" goes past 12.

    I will try making a new export and see if I can set a default date for the mutations.

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Different Date Formats In Same Column

    Sure, that's the case for some dates but what about others where both the day and month are ≤ 12 such as the one that I mentioned in post #4?

    If you are able to look at the data and know what the date is supposed to be, we can create a formula to convert it but I just don't see how you will be able to know if (for another example) 12/01/2017 is December 1st or January 12th.

    You can upload a workbook with your "dates" in a column and we can take a look at it. To upload a workbook, click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  7. #7
    Registered User
    Join Date
    11-14-2017
    Location
    Netherlands
    MS-Off Ver
    Office 2013 ProPlus
    Posts
    5

    Re: Different Date Formats In Same Column

    I have included a zip file with the excel file only containing the dates.
    Attached Files Attached Files

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Different Date Formats In Same Column

    Looking at your sample, everything that is left-justified is of the form mm/dd/yyyy and is being seen as text.

    Assuming that the dates (that are right-justified) were converted correctly, to convert the text dates, highlight column C > Data > Text to Columns > Date: MDY > Finish.

  9. #9
    Registered User
    Join Date
    11-14-2017
    Location
    Netherlands
    MS-Off Ver
    Office 2013 ProPlus
    Posts
    5

    Re: Different Date Formats In Same Column

    ah, it seems that you are right haha. Thanks a lot. Didnt think of that myself..

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Different Date Formats In Same Column

    You're welcome.

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

+ 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. Change all date formats in a column at once?
    By jahagafut in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2015, 11:52 AM
  2. [SOLVED] Multiple Date and Text Formats In One Column
    By nevi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-25-2014, 07:00 PM
  3. Various date formats in same column
    By mardiefe in forum Excel General
    Replies: 5
    Last Post: 06-22-2014, 09:42 AM
  4. Multiple date formats within a single column
    By the big frame in forum Excel General
    Replies: 3
    Last Post: 02-22-2012, 05:02 PM
  5. How to change multiple date formats in same column
    By muchado in forum Excel General
    Replies: 6
    Last Post: 11-10-2010, 01:37 AM
  6. [SOLVED] Copy Color Formats Based On Column Date Values
    By Naji in forum Excel General
    Replies: 0
    Last Post: 01-11-2006, 05:10 PM
  7. Copying formats - column widths, formats, outlining to worksheets
    By DavidBr318 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-17-2005, 09:05 AM

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