1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2003: Extracting and then converting text to date

Discussion in 'Business Applications' started by piltbot, Oct 13, 2011.

Thread Status:
Not open for further replies.
  1. piltbot

    piltbot Thread Starter

    Oct 13, 2011
    I am trying to extract a date from a data cell within an Excel 2003 workbook.

    The cell contains the data, "October 10, 2011 11:11:11 PM GMT-04:00" but I want only the date information and for it to convert to MM/DD/YYYY format. Basically, taking that cell and turning it into 10/10/2011.

    I have about 500 entries with similar data and I am looking for the easiest way to extract the dates from the text. Mind you, the length of the Months (i.e., January vs. September), Date (i.e., 1 vs 11), and even time vary (1:00 vs 11:11).

    Any help would be greatly appreciated!

    Found the answer I was looking for:

    Assuming the first cell is A1,

    =TEXT(LEFT(A1,FIND(",",A1)+5), "mm/dd/yyy")

    Copy down, then copy the column and paste values.

    You can also use

    =DATEVALUE(LEFT(A1,FIND(",", A1)+5))

    then format the cell as mm/dd/yyy, then copy down.
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1022107

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice