Converting Numbers to Months

[John]

Does anyone know the formula which will convert numbers into months for
example:

9-2008 and I need Sep-2008 have tried changing the column into a Custom type
using mmm-yy but it completely changes the month and year. The data has been
taken from our company operation system which is SAP.

Thanks

 

[Stefi]

9-2008 being in B1
=DATE(RIGHT(B1,4),LEFT(B1,SEARCH("-",B1)-1),1)
and custom format the result cell like mmm-yyyy

Regards,
Stefi

„John†ezt írta:

 

[Bob Phillips]

In an adjacent column, use

=DATEVALUE(A1)

and format as mmm-yyyy

 

[Ron Coderre]

Wtih
A1 containing "date-like" text....eg: 9-2008

This formula converts that value to a date:
B1: =--A1
(format B1 as a date)

or...this formula converts the A1 value into formatted text:
B1: =TEXT(A1,"mmm-yyyy")

In the above example, the first formula returns the date 09/01/2008,
(which can be formatted any way you'd like)
and the second formula returns this text: Sep-2008

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[Gord Dibben]

Data>Text to Columns>Next>Next>Column Data Format>Date>MYD

Works for me with 9-2008 returning Sep-2008 formatted as custom mmm-yyyy


Gord Dibben MS Excel MVP