Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Date format - Question

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am needing to turn 0102 into 01/02 . I have thousands of these that I need to convert.

    Is there an easy way to do this?

    Thank you,
    Jason

    PS: The new format is great! Maybe one day, I can make more of a contribution to the message board instead of always having to ask for help.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try with this formula:

    =DATE(RIGHT(A1,2)+((RIGHT(A1,2)<"29")*2000),LEFT(A1,2),1)

    I'm assuming this is in mmyy format. Also, if any year is below 29, i'm assuming it's 2000, else, 1900.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you for your response.

    You gave the year 2000 for the date and said that if it was 19** to use that. What if there is a combination of the two? Is there a way to differentiate between them?

    Muchismas gracias,
    Jason

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Do you mean if you have

    0110
    and
    0520

    The first one is 01/1910 and the second one is 05/2020 how to differentiate them ? well, manually... You have to set a line somewhere to differentiate between 2000 and 1900.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks again for the response.

    I am still having problems with the formula.

    This is what I am entering:

    =DATE(right (G2,2) + ((right(g2,2),"29")*2000), (Left(g2,2),1)

    The first cell that I have data in is located in G2.

    Any more suggestions?

    Thanks again,

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =TEXT(A1,"00/00")+0

  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok guys....I'm really starting to feel dumb here. For some reason, my results are not turning out the way I need them to.

    This is a part of the data set that I am working with:

    Matter Period
    1191
    1091
    1091
    0150

    This is Juan's formula as I have entered it:

    =DATE(RIGHT(G2,2)+((RIGHT(G2,2)<"29")*2000),LEFT(G2,2),1)

    These are the results I am getting for the same set:

    1/0/1900

    I am needing:

    Matter Period
    11/91
    10/91
    10/91
    01/50

    Or:

    Matter Period
    11/1991
    10/1991
    10/1991
    01/1950

    The reason I am needing it in this format is that I am working with Pivot Tables and the sort is not working properly, due to the format of the dates. My goal is to have these numbers in a format where I can sort my data by the date of the matter.

    This is how my Pivot Table is set up:

    Matter
    Date Owner Aty Total

    0100 COAT BARRY $5,890.00
    JAMES $13,919.50
    0101 COAT JAMES $37,093.12
    0102 COAT BARRY $6,613.50
    JAMES $71,849.50

    As you can see, it is sorting my data by the first numbers, or the month, and not the year. I have to have it sorted by year and month.

    Any more recommendations?

    I appreciate your help.

    Jason



  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-18 14:08, J Lowe wrote:
    Ok guys....I'm really starting to feel dumb here. For some reason, my results are not turning out the way I need them to.

    This is a part of the data set that I am working with:

    Matter Period
    1191
    1091
    1091
    0150

    This is Juan's formula as I have entered it:

    =DATE(RIGHT(G2,2)+((RIGHT(G2,2)<"29")*2000),LEFT(G2,2),1)

    These are the results I am getting for the same set:

    1/0/1900

    I am needing:

    Matter Period
    11/91
    10/91
    10/91
    01/50

    Or:

    Matter Period
    11/1991
    10/1991
    10/1991
    01/1950

    The reason I am needing it in this format is that I am working with Pivot Tables and the sort is not working properly, due to the format of the dates. My goal is to have these numbers in a format where I can sort my data by the date of the matter.

    This is how my Pivot Table is set up:

    Matter
    Date Owner Aty Total

    0100 COAT BARRY $5,890.00
    JAMES $13,919.50
    0101 COAT JAMES $37,093.12
    0102 COAT BARRY $6,613.50
    JAMES $71,849.50

    As you can see, it is sorting my data by the first numbers, or the month, and not the year. I have to have it sorted by year and month.

    Any more recommendations?

    I appreciate your help.

    Jason
    First, let me say that my earlier response was predicated on the belief that your dates were in MMDD format. Sorry for my misunderstanding. Now on to Juan's formula. Really, all you need is...

    =DATE(RIGHT(G2,2),LEFT(G2,2),1)

    Excel will automatically interpet the year
    based on the following...


    00-29 will be mapped to 2000
    30-99 will be mapped to 1900

    This is explained in the Help Topic for
    "year 2000".


    [ This Message was edited by: Mark W. on 2002-02-18 14:29 ]

  9. #9
    New Member
    Join Date
    Feb 2002
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That is so much closer. I found that I was trying to plug the formula into the formula bar, instead of just pasting on top of the number that I already have.

    Now, there is just one more problem. The format of these dates, with the formula you have given, is in mm/dd/yy, but I need it in mm/yy. Can this be done??

    Thank you all again.
    Jason

  10. #10
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-18 15:15, J Lowe wrote:
    That is so much closer. I found that I was trying to plug the formula into the formula bar, instead of just pasting on top of the number that I already have.

    Now, there is just one more problem. The format of these dates, with the formula you have given, is in mm/dd/yy, but I need it in mm/yy. Can this be done??

    Thank you all again.
    Jason
    Custom format your cells as mm/yy. From the main menu, Format|Cells. On the number tab, select custom and then type in mm/yy in the type box.
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •