#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 54
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 143
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2002 date entry: Cannot get away from d-m-yy entry format Steve Eklund Excel Discussion (Misc queries) 3 May 11th 09 04:57 PM
date entry medavino Excel Worksheet Functions 1 January 14th 08 07:08 PM
Date Entry Daniel L. Lieberman Excel Discussion (Misc queries) 1 May 3rd 07 04:13 AM
Restricting date entry relative to current date bwalshjr Excel Worksheet Functions 1 April 10th 07 12:49 PM
Date entry magstate Excel Discussion (Misc queries) 2 March 23rd 05 08:15 PM


All times are GMT +1. The time now is 11:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"