date & month

A

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
 
R

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
 
R

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
 
D

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.
 
R

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

date entry 10
Calculating Work Time 7
Reverse Index Match 2
display month only 4
simple week entry 2
Conditional Formatting - Dates 2
How to determine the value? 2
Grouping Dates by Month 4

Top