date & month
On Wed, 03 Jun 2009 05:38:49 -0400, Ron Rosenfeld
wrote:
On Tue, 2 Jun 2009 23:54:01 -0700, aditya
wrote:
In cell A1, data is of 19 or 20 or 21 digit whose last 6 digit is in form of
ddmmyy.
how can i get date and month r in cell A2 & A3 from this.
e.g. A1 A2
A3
NW/RON/ENT/05/010209 01 FEB 09 FEB
NW/SR-B/ENT/04/030509 03 MAY 09 MAY
To extract the last six characters and change it into a date, you can use this
formula:
=DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)
+100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30),
MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM(
RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2))
You can use that formula in A2 and A3 and custom format according to your
requirements, or you can wrap a TEXT function around the formula to give a
similar display (although one that you may not be able to use in any
calculations.
So, using the TEXT function method:
A2:
=TEXT(DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1, "/",REPT(" ",99)),99)),2)
+100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30),
MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM(
RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)),"dd mmm yy")
A3:
=TEXT(DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1, "/",REPT(" ",99)),99)),2)
+100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30),
MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM(
RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)),"mmm")
--ron
Stupid me.
Much simpler:
For the date:
=DATE(RIGHT(A1,2)+1900+100*(--RIGHT(A1,2)<30),
LEFT(RIGHT(A1,4),2),LEFT(RIGHT(A1,6),2))
and for the formatted date:
=TEXT(DATE(RIGHT(A1,2)+1900+100*(--RIGHT(A1,2)<30),
LEFT(RIGHT(A1,4),2),LEFT(RIGHT(A1,6),2)),"dd mmm yy")
and for the formatted month:
=TEXT(DATE(RIGHT(A1,2)+1900+100*(--RIGHT(A1,2)<30),
LEFT(RIGHT(A1,4),2),LEFT(RIGHT(A1,6),2)),"mmm")
--ron
|