+ Reply to Thread
Results 1 to 3 of 3

convert numbers to date

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    convert numbers to date

    I have downloaded a multi-row csv file which uses the following date format on each row:

    Please Login or Register  to view this content.
    This is a UK-style date which I want to convert to a simple US date in this format: 01/04/2013

    I've tried using "substitute" and "clean" to get a standard date which excel recognizes, but I'm only partially successful. One trick I've used in the past is to use "left" and "substitute" in order to get 04/01/2013. Sometimes I've used *1 which helps get it into a true number. But once the date hits 10/01/2013, excel no longer sees it as a date.

    Any advice?
    Thanks!

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: convert numbers to date

    If the format is consistent. Use below formula.
    =DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))

  3. #3
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: convert numbers to date

    Thanks! Yes that works for the entire dataset.

+ 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. Convert numbers appearing as a date into actual date in order to sort data
    By Santa1986 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-06-2015, 12:56 PM
  2. need to convert numbers to date
    By ssuman1010 in forum Excel General
    Replies: 3
    Last Post: 10-02-2015, 10:16 AM
  3. Convert numbers entered into a date
    By sjennings007 in forum Excel General
    Replies: 3
    Last Post: 05-07-2014, 06:01 PM
  4. [SOLVED] Formula to Convert numbers to date
    By [email protected] in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-03-2012, 10:29 AM
  5. Convert numbers to date in text string
    By singerbatfink in forum Excel General
    Replies: 2
    Last Post: 09-21-2010, 05:58 AM
  6. Replies: 1
    Last Post: 03-31-2010, 05:03 PM
  7. extract numbers, convert to date
    By gkaspen in forum Excel General
    Replies: 8
    Last Post: 03-01-2005, 11:08 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