Converting Numbers to Months
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 |
Converting Numbers to Months
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: 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 |
Converting Numbers to Months
In an adjacent column, use
=DATEVALUE(A1) and format as mmm-yyyy -- __________________________________ HTH Bob "John" wrote in message ... 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 |
Converting Numbers to Months
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 "John" wrote: 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 |
Converting Numbers to Months
DataText to ColumnsNextNextColumn Data FormatDateMYD
Works for me with 9-2008 returning Sep-2008 formatted as custom mmm-yyyy Gord Dibben MS Excel MVP On Wed, 10 Sep 2008 03:10:05 -0700, John wrote: 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 |
All times are GMT +1. The time now is 08:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com