date entry

[aditya]

In cell A1, data is of 10 digit whose last 6 digit is in form of ddmmyy.
how can i get date, month and year in cell A2 from this.

e.g. A1 A2
UPR/010209 01 FEB 09

 

[Jarek Kujawa]

format as "dd mmm yy"

=DATE("20"&RIGHT(A1,2),MID(A1,7,2),MID(A1,FIND("/",A1)+1,2))

this formula will give you "01 feb 09"

pls click YES if this helped

 

[Sean Timmons]

=DATE(2000+RIGHT(A1,2),MID(A1,7,2),MID(A1,5,2))

 

[David Biddulph]

If ddmmyy is the format of short dates in your Windows regional settings,
then use
=--TEXT(RIGHT(A1,6),"00\-00\-00") and format as date.

 

[Jacob Skaria]

Assuming the century will always be 20

=DATE(VALUE("20" & RIGHT(A1,2)),MID(A1,7,2),MID(A1,5,2))

If this post helps click Yes

 

[aditya]

It works well if data is there in A1 but shows value error when A1 cell is
blank.
i want the cell blank if A1 is blank.
what should i do?

 

[aditya]

It works well if data is there in A1 but shows value error when A1 cell is
blank.
i want the cell blank if A1 is blank.
what should i do?

 

[aditya]

It works well if data is there in A1 but shows value error when A1 cell is
blank.
i want the cell blank if A1 is blank.
what should i do?

 

[Jarek Kujawa]

=IF(LEN(A1)=0,"",DATE("20"&RIGHT(A1,2),MID(A1,7,2),MID(A1,FIND
("/",A1)+1,2)) )

 

[Sean Timmons]

=IF(A1="","",DATE(2000+RIGHT(A1,2),MID(A1,7,2),MID(A1,5,2)))

 

[Jarek Kujawa]

simpler

=IF(LEN(A1),DATE("20"&RIGHT(A1,2),MID(A1,7,2),MID(A1,FIND
("/",A1)+1,2)),"")