+ Reply to Thread
Results 1 to 5 of 5

Unify Dates - Mixed text and Date

  1. #1
    Registered User
    Join Date
    03-08-2019
    Location
    UK
    MS-Off Ver
    20019
    Posts
    3

    Unify Dates - Mixed text and Date

    HI
    I have a column start date and end date. Trying to use DATEDIF now.
    Problem is 50% of date roughly is a text and function does not calculate at all.

    Just choosing date format does not do the job. What would be fastest way to solve it ? Macro ?

    Regards
    Dominik

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Unify Dates - Mixed text and Date

    Bring the file into Power Query. Change the date columns to Date. Close and Load back to Excel.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    03-08-2019
    Location
    UK
    MS-Off Ver
    20019
    Posts
    3

    Re: Unify Dates - Mixed text and Date

    Don't seem to have Power Query - I think I got office 365
    I have found Power Pivot

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Unify Dates - Mixed text and Date

    I'm having trouble making a date into text but maybe if you try to add this to the datedif formula... =DATEDIF(A3,--B3,"d") or perhaps =DATEDIF(A3,B3*1,"d") to whichever is the text value?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Unify Dates - Mixed text and Date

    If your mixed "real"dates and "text that looks like a date" are in col-A starting at A1, then use left() / mid() / right() to extract year, month and day from the "text that looks like a date" and then use the date() function to convert to a real date.

    If isnumber(A1) returns true then you already have a real date value.

    So in B1 something like: if(isnumber(A1), A1, date("year text", "month text", "day text")

    Where "year text" is extracted from A1 using left()/mid()/right() as necessary.

+ 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] mixed dates to harmonize
    By seraphin in forum Excel General
    Replies: 3
    Last Post: 02-27-2019, 10:22 PM
  2. Help in formatting mixed dates
    By mwot in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-11-2018, 02:39 AM
  3. dates appearing as generic code when mixed with text in a formula
    By mtleigh in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-06-2016, 05:29 AM
  4. Formula to Unify Text Entries in the Same Column
    By Taisir in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 12-22-2015, 03:01 AM
  5. check for date in mixed text/date and format cell.. Help!
    By berbchid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-24-2013, 03:42 AM
  6. Mixed Text and cell ref - Problem with date formatting
    By maacmaac in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-01-2007, 07:31 PM
  7. how to clear up mixed date/text column
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-10-2006, 07:40 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