+ Reply to Thread
Results 1 to 11 of 11

Converting non-standard dates

  1. #1
    Forum Contributor heytherejem's Avatar
    Join Date
    07-06-2017
    Location
    Hampshire, England
    MS-Off Ver
    2016
    Posts
    138

    Cool Converting non-standard dates

    I'm having a nightmare.

    The background is that I have an excel table containing audit data. The date row contains dates either in mm/dd/yyyy or mm-dd-yyyy.

    I want them all to be dd/mm/yyyy (I am in the UK and the date is in US format). I've tried =Text(_,"dd/mm/yyyy) which converts the forward slash dates / fine, but nothing I do works on the hyphen - dates. Even substituting the - for a / creates errors.

    Can anyone give me a formula that will convert it all in one go?

    Thanks in advance :-)

    Jemma

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,035

    Re: Converting non-standard dates

    Hi Jemma & Welcome to the Forum,

    Try...

    Text to Columns
    • Highlight range to convert
    • Data >> Data Tools >> Text to Columns >> Next >> Next
    • Toggle Date: DMY >> Finish
    HTH
    Regards, Jeff

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,808

    Re: Converting non-standard dates

    Assuming you have dates in A1

    Try

    B1
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Forum Contributor heytherejem's Avatar
    Join Date
    07-06-2017
    Location
    Hampshire, England
    MS-Off Ver
    2016
    Posts
    138

    Re: Converting non-standard dates

    Hi Shukla,

    Thanks for the formula - it's worked on the date values which were mm-dd-yyyy but the ones which were mm/dd/yyyy (so actually fine and just needed changing round via date format functionality) now have a #VALUE! error. Ideally I am after a formula which works across the whole column as I am trying to automate a process.
    Would some sort of iferror addition to the formula work perhaps?

    Thanks,
    Jemma

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,035

    Re: Converting non-standard dates

    heytherejem,

    Perhaps you could attach a sample workbook...

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  6. #6
    Forum Contributor heytherejem's Avatar
    Join Date
    07-06-2017
    Location
    Hampshire, England
    MS-Off Ver
    2016
    Posts
    138

    Re: Converting non-standard dates

    I've attached the sample data. So there are two date formats in row B.
    I've inserted date C, into which I would like a standard date format based on row B data.
    I would like whatever formula I use to standardise the dates, to work across the whole column.

    Many thanks,
    Jemma
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: Converting non-standard dates

    Hi Jemma,

    Opening in my UK Excel it changed the "/" dates into an actual date number, then displays as UK format. I'm not sure if this formula will work for the original data but feel free to give it a try:
    =DATE(RIGHT(TEXT([@[Date Audited]],"dd-mm-yyyy"),4),LEFT(TEXT([@[Date Audited]],"dd-mm-yyyy"),2),MID(TEXT([@[Date Audited]],"dd-mm-yyyy"),4,2))

    Ideally you will want to change the dates around whilst they are still text in the original file, before you put them into Excel and it tries to change them to date serial numbers.

  8. #8
    Forum Contributor heytherejem's Avatar
    Join Date
    07-06-2017
    Location
    Hampshire, England
    MS-Off Ver
    2016
    Posts
    138

    Re: Converting non-standard dates

    Stormin'

    It kinda works, the date are all in the right format, but as per the attached it's converted the dates that were in UK format (dd/mm/yyyy) into a completely other date - 2018 and 2019 because it's confused and thinks 13/12/2016 is the 13th month so Jan 2017 and the 12 is the date.
    So, i'm thinking - can we include an =IF( function or something similar so that your formula only operates on the mm-dd-yyyy dates and simply copies over the value from the dd/mm/yyyy dates?

    Attached Files Attached Files

  9. #9
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: Converting non-standard dates

    Yeah the problem here is that when I open the file, Excel sees the date strings with "/" in them as dates and converts them for me (yay).

    Try:
    =IF(IFERROR(FIND("-",TEXT([@[Date Audited]],"@")),0),DATE(RIGHT(TEXT([@[Date Audited]],"@"),4),LEFT(TEXT([@[Date Audited]],"@"),2),MID(TEXT([@[Date Audited]],"@"),4,2)),DATEVALUE([@[Date Audited]]))

  10. #10
    Forum Contributor heytherejem's Avatar
    Join Date
    07-06-2017
    Location
    Hampshire, England
    MS-Off Ver
    2016
    Posts
    138

    Re: Converting non-standard dates

    Stormin'

    Your formula worked () I had to change DATEVALUE to VALUE for the IFERROR argument to work. You've been really helpful. Thank you so much!!

    Jemma

  11. #11
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: Converting non-standard dates

    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. Thanks.

+ 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. Converting standard text to hh:mm:ss
    By c00ly81 in forum Excel General
    Replies: 5
    Last Post: 04-29-2015, 05:12 AM
  2. Error after converting standard workbook to add in
    By IainMcK12345 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-14-2014, 12:14 PM
  3. Replies: 4
    Last Post: 05-10-2014, 10:45 AM
  4. [SOLVED] Converting Standard Time format (hh:mm:ss) to number of minutes
    By xygrax in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-27-2014, 10:54 AM
  5. Converting all date formats to one single standard format.
    By booo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-25-2010, 07:14 PM
  6. Replies: 2
    Last Post: 10-03-2009, 05:47 PM
  7. Converting name to standard time
    By sridharKj in forum Excel General
    Replies: 1
    Last Post: 03-21-2005, 12:38 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