Help with EXCEL

[Aimster]

So who can do this?

Convert 0001 to 01-2000 or January 2000

Anyone know the formula? This is a tricky one.

 

[Aimster]

wrong forum move to OFF TOPIC

 

[Blackjack200]

Vote for Obama and help convert 1-21-09 into a happy day!

ahem...

 

[sandorski]

Originally posted by: Blackjack200
Vote for Obama and help convert 1-21-09 into a happy day!

ahem...

Yes, Obama for Change. Or McCain for Change that isn't.

 

[Duwelon]

You could do this with VBA like this:

Function convert1()

inputData = "0001"
Month1 = Right(inputData, 2)
Year1 = Left(inputData, 2)

DateFinal = DateSerial(Year1, Month1, 1)
DateFormatted = Format(DateFinal, "MM-YYYY")
MsgBox DateFormatted

End Function

Not sure about cell formulas, but this could be altered to convert any number of rows that you wanted. Do you have a certain range of rows or columns you need converted?

PS, i'd rather sit through a round of russian roulette than see Obama elected.

 

[MiniGolfIsFun]

Or you can use text to columns and then do a concatenation of what you want

 

[KingGheedora]

The question you must ask yourself is this: Are you sure you want to participate in anything that requires this particular help with "EXCEL"?

 

[Jeff7]

How is the data entered? Is it already in the sheet, or is it yet to be entered?

What I did:

1) Format a column (Format Cells)
2) Under the Number tab, under Category, select "Custom."
3) Enter YYMM in the Type field.
4) Type in all your data in the 0001 format into the column, or else do a Paste-Special -> Values
5) Go back to that Custom format thing for the entire column or set of data.
6) Change format to MM-YYYY, and click OK.
0001 should now say "01-1900."


Alternate to step 5) :
5) Go to the Format Cells window again, but under Category, choose "Date."
6) Select a type from the list. I show one there that says March 14, 1998. If I choose that, the cell now says "January 1, 1900."

 

[Aimster]

Here is the tricky part:

I want 0001 to read as the year 2000 month January.

It won't convert it properly

0204 for example would be April, 2004.

 

[puffff]

Originally posted by: Aimster
Here is the tricky part:

I want 0001 to read as the year 2000 month January.

It won't convert it properly

0204 for example would be April, 2004.

=date(left(A1,2) + 2000, right(A1,2), 1)

I think that would work.

 

[Aimster]

nope

Still not working.

With that formula it says 0001 is year 2001. Not 2000.

Is this a flaw in Excel?

 

[JujuFish]

Originally posted by: Aimster
0204 for example would be April, 2004.

 

[petesamprs]

The formula you need is: =DATE(MID(A1,1,2)+2000,MID(A1,3,2),1)

Then, depending how you want the output to show up, right click on the cell, go to "Number", "Custom" and under "Type":
- type "mm-yyyy" to display as "04-2002"
- type "mmmm yyyy" to display as "April 2002"

 

[Aimster]

Doesn't work!

I want 0204 to Display as April, 2002!

or 0212 to Display as December, 2002

It says 2020!

 

[PowerEngineer]

tada...

=TEXT(DATE(MID(TEXT(A1,"0000"),1,2)+2000,MID(TEXT(A1,"0000"),3,2),1),"mmmm yyyy")

 

[Aimster]

Originally posted by: PowerEngineer

tada...

=TEXT(DATE(MID(TEXT(A1,"0000"),1,2)+2000,MID(TEXT(A1,"0000"),3,2),1),"mmmm yyyy")

you sir are genius