+ Reply to Thread
Results 1 to 12 of 12

extracting dates

  1. #1
    Registered User
    Join Date
    06-21-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    56

    extracting dates

    Hi!

    So in cell A2 I have a date which is written as: mon sept 6 fri sep 24

    In cell B2, I have it set up to where it extracts the month (which would be September) and in cell C2, it extracts the date (which would be 6). But if there are 2 different months inside the cell it does it perfectly.

    I was wondering if anyone knew how I can extract both dates dates for september.

    I'll post up sample data that I have.

    Thanks for all the hlep!
    Attached Files Attached Files
    Last edited by longhorn23; 08-18-2010 at 01:34 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: extracting dates

    So you're looking to extract just the first date, correct ?

    edit: Is Sept definitely correct - it's not Sep ?

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: extracting dates

    Perhaps - having thought about it:

    B2: =TEXT(1&MID(A2,FIND(" ",A2)+1,3),"mmmm")
    C2: =0+MID(A2,FIND("^^",SUBSTITUTE(A2," ","^^",2))+1,2)

    does that work for you ?

    For the 2nd Date

    D2: =TEXT(1&MID($A2,FIND("^^",SUBSTITUTE($A2," ","^^",4))+1,3),"mmmm")
    E2: =0+MID($A2,FIND("^^",SUBSTITUTE($A2," ","^^",5))+1,2)
    Last edited by DonkeyOte; 08-18-2010 at 01:13 PM.

  4. #4
    Registered User
    Join Date
    06-21-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: extracting dates

    Thanks for the quick reply!

    I posted up a sample of what my data looks like. Take a look at the attchment.

    Basically from one cell where there it has 2 dates like "sep 7 fri sep 10"
    i need a way to figure out how to extract both of those dates in order for column's X and Y (in the attachment) to have appropriate Start and End Dates.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: extracting dates

    Using your sample file and assuming US config you should find the below can be applied as follows:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-21-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: extracting dates

    Wow!

    That actually worked fine. But there was one last discreprency.
    Sometimes in column A I have values which that look like: "wed aug 4"

    Which means the start and end dates should be the same. Any way to fix this?
    I'll update the test1.xls file to accodomate this.


    THANKS again for the help!!
    Attached Files Attached Files

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: extracting dates

    A quick fix would be to repeat the string twice over in each instance

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    06-21-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: extracting dates

    NICE!!! thanks soo much!

    It worked perfectly. Wow you're really good at this.

    By the way do you happen to know how to get rid of "#VALUE!" in cells I have? I have the following code but I keep getting an error:

    Please Login or Register  to view this content.
    Last edited by longhorn23; 08-18-2010 at 03:06 PM.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: extracting dates

    Please try to keep threads dedicated to a single theme.

    Your latest question would be best placed in a new thread -- but yes there are quick ways to do this (and no row 0)

    Thanks,

  10. #10
    Registered User
    Join Date
    06-21-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: extracting dates

    oh. Sorry

    I switched it from 0 to 2 actually, but it still wont work. I'll just start a new thread.

    Thanks for all the help though!

  11. #11
    Registered User
    Join Date
    06-21-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2007
    Posts
    56

    Re: extracting dates

    Hi DonkeyOte.

    I was wondering if there was a way in the formula you gave me

    =DATEVALUE(MID(TRIM($U2&" "&$U2),FIND("^^",SUBSTITUTE(TRIM($U2&" "&$U2)," ","^^",CHOOSE(COLUMNS($V2:V2),2,5)))+1,2)&MID(TRIM($U2&" "&$U2),FIND("^^",SUBSTITUTE(TRIM($U2&" "&$U2)," ","^^",CHOOSE(COLUMNS($V2:V2),1,4)))+1,3))


    Is there a way to put an IsError in there so whenever there is a #VALUE! it'll just show a blank cell (with the formula still inside of it).

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: extracting dates

    Given use of XL2007 you can encase within an IFERROR

    =IFERROR(<formula>,"")

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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