Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > date entry
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 54
Default date entry
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 896
Default date entry
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


On 27 Maj, 14:23, aditya wrote:
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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,696
Default date entry
=DATE(2000+RIGHT(A1,2),MID(A1,7,2),MID(A1,5,2))

"aditya" wrote:

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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8,651
Default date entry
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.
--
David Biddulph

"aditya" wrote in message
...
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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8,520
Default date entry
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
---------------
Jacob Skaria


"aditya" wrote:

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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 54
Default date entry
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?

"Sean Timmons" wrote:

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

"aditya" wrote:

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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 54
Default date entry
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?

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"aditya" wrote:

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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 54
Default date entry
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" wrote:

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


On 27 Maj, 14:23, aditya wrote:
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


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 896
Default date entry
=IF(LEN(A1)=0,"",DATE("20"&RIGHT(A1,2),MID(A1,7,2) ,MID(A1,FIND
("/",A1)+1,2)) )


On 28 Maj, 11:58, aditya wrote:
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" wrote:
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

On 27 Maj, 14:23, aditya wrote:
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- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,696
Default date entry
=IF(A1="","",DATE(2000+RIGHT(A1,2),MID(A1,7,2),MID (A1,5,2)))

"aditya" wrote:

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?

"Sean Timmons" wrote:

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

"aditya" wrote:

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
Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 896
Default date entry
simpler

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



On 28 Maj, 15:42, Jarek Kujawa wrote:
=IF(LEN(A1)=0,"",DATE("20"&RIGHT(A1,2),MID(A1,7,2) ,MID(A1,FIND
("/",A1)+1,2)) )

On 28 Maj, 11:58, aditya wrote:



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" wrote:
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

On 27 Maj, 14:23, aditya wrote:
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- Ukryj cytowany tekst -

- Pokaż cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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 06:08 PM
Restricting date entry relative to current date bwalshjr Excel Worksheet Functions 1 April 10th 07 12:49 PM
Date entry excel-duh?[_2_] Excel Discussion (Misc queries) 3 April 7th 07 11:08 PM
Date entry magstate Excel Discussion (Misc queries) 2 March 23rd 05 07:15 PM


All times are GMT +1. The time now is 04:49 PM.

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"

 

Contact Us ExcelBanter forum home Privacy Statement