+ Reply to Thread
Results 1 to 9 of 9

Extract only date into new column

Hybrid View

  1. #1
    Registered User
    Join Date
    01-25-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    3

    Extract only date into new column

    I have a report extracted from shop software into Excel 2010. Unfortunately in column A I have job number (in text format) in row 1 and due date (in date format) in row 2 and so on...

    COLUMN A
    ROW1 Job No
    ROW2 Due Date
    ROW3 121033
    ROW4 01/24/13
    ROW5 121018
    ROW6 01/31/13
    ROW7 121019
    ROW8 01/31/13

    I would like to have them separated, or at least COLUMN B showing me only date (DUE DATE), have no need for JOB number (Job No)

    I tried this IF function:

    =IF(ISERROR(DATEVALUE),TEXT(A17,"mm/dd/yyyy")," ")
    but it turns the job number (in text format) into a date

    any ideas, my brain is either overthinking this, or I'm really in lack of coffee to see the simple solution.

  2. #2
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Extract only date into new column

    Hello,

    What you can do is add a *1 on it
    =IF(ISERROR(DATEVALUE),TEXT(A17,"mm/dd/yyyy")*1,"")
    This will convert it back to number.
    Another approach is paste the following formula in B1
    =INDIRECT("A"&(ROW())*2)
    And drag it down till you see 0 / error. What this does is taking value from any cell in A with even row number (2, 4, 6, ...)
    Last edited by Lemice; 04-24-2013 at 11:56 AM.
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  3. #3
    Registered User
    Join Date
    01-25-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Extract only date into new column

    Formula: copy to clipboard
    =IF(ISERROR(DATEVALUE),TEXT(A17,"mm/dd/yyyy")*1,"")

    I get: #VALUE!

    Formula: copy to clipboard
    =INDIRECT("A"&(ROW())*2)

    I get: 0
    but I need the row with the date to be shown in COLUMN B as date

    Formula: copy to clipboard
    =IFERROR(INDEX($A$1:$A$52,ROWS($A$1:A1) *2),"")

    I get: 0 as well on all rows

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Extract only date into new column

    Assuming job number in row 1 and odd numbered rows

    =IFERROR(INDEX($A$1:$A$52,ROWS($A$1:A1) *2),"")
    copied down
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Extract only date into new column

    Make sure you are pointing the reference in the formula to the right place (Like A3 instead of A17)

    To make it look like date, you can format it by pressing Ctrl-1, choose "Date" (Or to Format --> Cell, they are the same)

    here is a sample file illustrating all three formulas
    Column B is using =IF(ISERROR(DATEVALUE),TEXT(A17,"mm/dd/yyyy")*1,"")
    Column C is using =INDIRECT("A"&(ROW())*2)
    Column D is using =IFERROR(INDEX($A$1:$A$52,ROWS($A$1:A1) *2),"")
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Extract only date into new column

    Where are your dates specifically?
    See attachment
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-25-2012
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Extract only date into new column

    It works:

    121033 1/24/2013
    01/24/13 1/31/2013
    121018 1/31/2013
    01/31/13 1/31/2013
    121019 2/11/2013
    01/31/13 4/1/2013
    121020 4/5/2013
    01/31/13 4/9/2013
    121205-2 4/19/2013
    02/11/13 4/22/2013
    130204 4/23/2013
    04/01/13 5/1/2013
    130328 5/3/2013
    04/05/13 5/6/2013


    however (sorry) I need the row where the job no is(ex 121033) to be left blank, as I have amount on column C that relates to the due date.
    121033
    01/24/13 $1
    121018
    01/31/13 $2
    121019
    01/31/13 $3
    121020
    01/31/13 $4
    121205-2



    Ultimately what I'm trying to achieve is a graph of Sales figure by due date and reason why I need to separate column A due date from Job No (Number). I hope this makes sense.

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Extract only date into new column

    Can you upload a sheet showing what you want? (Go Advanced>Manage Attachments)

  9. #9
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Extract only date into new column

    Is this what you want then?

    (By the way, you should change you question / thread name to something like change non-date to blank or something, because you are doing exactly that)

    I'm cheating, yes, literally. So make sure you don't have any date with the year greater than 2100, or the date is formatted / entered as text, because the formula will not work properly and might return the wrong results.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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