Trying to convert mm/yy in text to Dec-07, it adds day & curre
in [Functions]
|
From: Excellency on 24 Mar 2010 15:32 This is the outcome..any suggestions Date Code 10/09 Oct-09 8/07 #VALUE! 11/06 Nov-06 3/08 #VALUE! 12/08 Dec-08 3/09 #VALUE! 12/08 Dec-08 "Héctor Miguel" wrote: > hi (again), Keddy ! > > >> I used your recommendation this the output. It displays current year on instead of 2009, 2007 or 2006 > >> > >> 10/09 = Oct - 10 > >> 08/07 = Aug-10 > >> 11/06 = Nov-10 > >> > >> thanks > >> Keddy > > also this one: =date(20&right(a1,2),left(a1,2),1) > and apply a custom number format (i.e.) mmm-yy > > hth, > hector. > > > . >
From: Excellency on 24 Mar 2010 15:34 this is the outcome on this formula..it works but puts it in this format, formatting does not change or do anything Date Code 10/09 Oct-09 8/07 2007/8//01 11/06 Nov-06 3/08 2008/3//01 12/08 Dec-08 3/09 2009/3//01 12/08 Dec-08 "Héctor Miguel" wrote: > hi, Keddy ! > > > I used your recommendation this the output. It displays current year on instead of 2009, 2007 or 2006 > > > > 10/09 = Oct - 10 > > 08/07 = Aug-10 > > 11/06 = Nov-10 > > > > thanks > > Keddy > > try this one: =text(20&right(a1,2)&"/"&left(a1,2)&"/01","mmm-yy") > > hth, > hector. > > > . >
From: H�ctor Miguel on 24 Mar 2010 15:44 hi, Keddy ! just... decide if the month part... will be allways two digits (as per your previous example: 08/07 = Aug-10) or could it be only one digit (as per example in this post: 8/07 #VALUE!) -???- so you could change/adapt/... the "left" (function part) in proposed formulae consider also *IF* the date-separator will be allways " / " -???- if any doubts (or further information)... would you please comment ? regards, hector. p.s. same to your "next" reply __ OP __ > This is the outcome..any suggestions > Date Code > 10/09 Oct-09 > 8/07 #VALUE! > 11/06 Nov-06 > 3/08 #VALUE! > 12/08 Dec-08 > 3/09 #VALUE! > 12/08 Dec-08 __ previous __ >> hi (again), Keddy ! >> >>>> I used your recommendation this the output. It displays current year on instead of 2009, 2007 or 2006 >>>> >>>> 10/09 = Oct - 10 >>>> 08/07 = Aug-10 >>>> 11/06 = Nov-10 >>>> >>>> thanks >>>> Keddy >> >> also this one: =date(20&right(a1,2),left(a1,2),1) >> and apply a custom number format (i.e.) mmm-yy
From: H�ctor Miguel on 24 Mar 2010 15:52
hi (again), Keddy ! try with this changes: op1: =text(20&right(a1,2)&"/"&left(a1,search("/",a1)-1)&"/01","mmm-yy") op2: =date(20&right(a1,2),left(a1,search("/",a1)-1),1) (cell format as date: mmm-yy) hth, hector. __ previous __ > just... decide if the month part... > will be allways two digits (as per your previous example: 08/07 = Aug-10) > or could it be only one digit (as per example in this post: 8/07 #VALUE!) -???- > > so you could change/adapt/... the "left" (function part) in proposed formulae > consider also *IF* the date-separator will be allways " / " -???- > > if any doubts (or further information)... would you please comment ? > regards, > hector. > > p.s. same to your "next" reply > > __ OP __ >> This is the outcome..any suggestions >> Date Code >> 10/09 Oct-09 >> 8/07 #VALUE! >> 11/06 Nov-06 >> 3/08 #VALUE! >> 12/08 Dec-08 >> 3/09 #VALUE! >> 12/08 Dec-08 > > __ previous __ >>> hi (again), Keddy ! >>> >>>>> I used your recommendation this the output. It displays current year on instead of 2009, 2007 or 2006 >>>>> >>>>> 10/09 = Oct - 10 >>>>> 08/07 = Aug-10 >>>>> 11/06 = Nov-10 >>>>> >>>>> thanks >>>>> Keddy >>> >>> also this one: =date(20&right(a1,2),left(a1,2),1) >>> and apply a custom number format (i.e.) mmm-yy |