Date format - Question

[J Lowe]

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.

[Juan Pablo González]

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.

[J Lowe]

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

[Juan Pablo González]

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.

[J Lowe]

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,

[Mark W.]

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

[J Lowe]

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

[Mark W.]

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 ]

[J Lowe]

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

[Barrie Davidson]

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.