Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
date entry new
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
date entry new
you maybe want formula like
=DATE(RIGHT(RIGHT(A1,6),2),MID(RIGHT(A1,6),3,2),LE FT(RIGHT(A1,6),2)) in cell b1 and copy down next to data in col A. "aditya" wrote in message ... | 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
date entry new
You may need to translate the year to the desired century. When I tried, 09
was treated as 1909. -- David Biddulph "Homey" <none wrote in message ... you maybe want formula like =DATE(RIGHT(RIGHT(A1,6),2),MID(RIGHT(A1,6),3,2),LE FT(RIGHT(A1,6),2)) in cell b1 and copy down next to data in col A. "aditya" wrote in message ... | 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
date entry new
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 --------------- Jacob Skaria "aditya" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 date entry: Cannot get away from d-m-yy entry format | Excel Discussion (Misc queries) | |||
date entry | Excel Worksheet Functions | |||
Date Entry | Excel Discussion (Misc queries) | |||
Restricting date entry relative to current date | Excel Worksheet Functions | |||
Date entry | Excel Discussion (Misc queries) |