date & month

[aditya]

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

 

[Roger Govier]

Hi

I am assuming that you really mean you want the Date in B1 and the Month in
C1.
In B1 enter
=RIGHT(A1,6)
Copy down as far as required
Copy column B>Paste Special>Values
Select column B>Data>Text to Columns>Next>Next>Date>D/M/Y>Finish

In cell C1
=TEXT(B1,"mmm")
Copy down as required

 

[Ron Rosenfeld]

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

 

[David Biddulph]

Or if you have your Windows Regional Options set to recognisedates as
ddmmyy, you can simplify Ron's A2 formula to
=--TEXT(RIGHT(A1,6),"00\-00\-00") and format as date, and A3 can (in either
case) be =TEXT(A2,"mmm").
You may need to use UPPER() if you insist on upper case.

 

[Ron Rosenfeld]

Or if you have your Windows Regional Options set to recognisedates as
ddmmyy, you can simplify Ron's A2 formula to
=--TEXT(RIGHT(A1,6),"00\-00\-00") and format as date, and A3 can (in either
case) be =TEXT(A2,"mmm").
You may need to use UPPER() if you insist on upper case.

Of course, that solution is critically dependent on the Windows Regional
settings. It won't work, for example, with standard US settings. The solution
I offered should work no matter how the Windows Regional settings are set.
--ron

 

[Ron Rosenfeld]

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

 

[David Biddulph]

Agreed. Hence the first clause of my reply.