date entry new

[aditya]

Data in column A is of 8/9/10 or 11 digit.
However in every case last 6 digit is in form of ddmmyy. how can i get date
from this in column B.

 

[Homey]

you maybe want formula like
=DATE(RIGHT(RIGHT(A1,6),2),MID(RIGHT(A1,6),3,2),LEFT(RIGHT(A1,6),2))
in cell b1 and copy down next to data in col A.

| Data in column A is of 8/9/10 or 11 digit.
| However in every case last 6 digit is in form of ddmmyy. how can i get
date
| from this in column B.

 

[David Biddulph]

You may need to translate the year to the desired century. When I tried, 09
was treated as 1909.

 

[Jacob Skaria]

Hi Aditya

Welcome with another date query !!

If you are sure your data do not have future dates....then use the below
which will consider the century as 19 for any year above current year. If
less than current year century 20 is considered..

=DATE(IF(RIGHT(RIGHT(A1,6),2)>RIGHT(YEAR(TODAY()),2),"19","20")&RIGHT(RIGHT(A1,6),2),MID(RIGHT(A1,6),3,2),MID(RIGHT(A1,6),1,2))6),2)

If all dates are in 20 th century then try
=DATE(2000+RIGHT(RIGHT(A1,6),2),MID(RIGHT(A1,6),3,2),MID(RIGHT(A1,6),1,2))


If this post helps click Yes