date entry new

A

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

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

David Biddulph

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

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
 

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

Top