+ Reply to Thread
Results 1 to 5 of 5

How to change text date (eg. July) to actual date format?

  1. #1
    Registered User
    Join Date
    11-25-2014
    Location
    Isle of Man
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    82

    How to change text date (eg. July) to actual date format?

    Is there a formula that would allow me to change a date such as: 'July 9 2015' to '09/07/2015' ?
    Likewise, also could this work for '9 July 2015' to '09/07/2015' ?

    Two separate formulas would be fine, I'm not sure if there is such a thing though?

    I understand if this is not possible, I haven't found any solutions online so far..

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to change text date (eg. July) to actual date format?

    For the first example:

    =DATE(RIGHT(A1,4),MONTH(1&LEFT(A1,3)),MID(A1,FIND(" ",A1)+1,2))

    for the second

    =--SUBSTITUTE(A1," ","-")

    or

    DATE(RIGHT(A1,4),MONTH(1&MID(A1,FIND(" ",A1)+1,3)),LEFT(A1,2))

    Both formula worked form me to convert text to dates.
    Last edited by AlKey; 07-11-2015 at 12:22 PM. Reason: Corrected formula per JohnTopley's comments
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    11-25-2014
    Location
    Isle of Man
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    82

    Re: How to change text date (eg. July) to actual date format?

    Wow, this worked! Brilliant :D Thank you loads, I was sure there probably wasn't going to be a way to do it, you're the best ^_^

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: How to change text date (eg. July) to actual date format?

    I think the first formula should be:

    =DATE(RIGHT(A1,4),MONTH(1&LEFT(A1,3)),MID(A1,FIND(" ",A1)+1,2))

    Try the original with July 10 2015

  5. #5
    Registered User
    Join Date
    11-25-2014
    Location
    Isle of Man
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    82

    Re: How to change text date (eg. July) to actual date format?

    Well spotted! I tested this out and you're right..thank you for your help! :D

+ 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. Replies: 4
    Last Post: 09-19-2014, 11:26 AM
  2. Change text date 22OCT2013 to actual date
    By grattis in forum Excel General
    Replies: 14
    Last Post: 12-19-2013, 08:32 AM
  3. [SOLVED] COnverting an incorrect date format to an actual date
    By Stuwork in forum Excel General
    Replies: 3
    Last Post: 10-24-2012, 06:16 AM
  4. Change date serial to actual date numbers
    By thompssc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2012, 12:47 PM
  5. Converting Text Date to Actual Date using VBA
    By reggie1000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-06-2010, 12:20 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