Date format - Question

J Lowe

New Member
Joined
Feb 17, 2002
Messages
27
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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,
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top