how to formulate Mth-Year & Year-Mth base on yyyymmdd ?

[William Poh Ben]

Hi,

Appreciate very much if someone can help me here. Thanks.

What formula to use in Excel column B and column C so that the output
results in column B and column C can be automatically formulated base
on the given dates data in column A ?


Column A Column B Column C

Date Month-Year Year-Month
20021111 Nov-02 200211
20021020 Oct-02 200210
20030226 Mar-03 200303
20030709 Jul-03 200307
20021220 Dec-02 200212

 

[Dave Peterson]

You could use a formula like this to get the date:

=DATE(LEFT(A3,4),MID(A3,5,2),RIGHT(A3,2))

Then use Format|cells|Number tab and use a custom format of:

"MMM-YY" w/o the quotes
and
"yyyymm" also without the quotes.

But if you just want a text string in there:
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"mmm-yy")
and
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"yyyymm")

But now those cells aren't dates anymore.

 

[acw]

William

In cell B2, enter the formula
=DATEVALUE(RIGHT(A2,2) & "/" & MID(A2,5,2) & "/" & LEFT
(A2,4)). Format the cell Custom, mmm-yy

In cell C2 enter the formula
=LEFT(A2,6) if you want it as text or
=VALUE(LEFT(A2,6)) if you want it as a number.

Tony