date

[bee123]

I have a personal number 1004862345 here.Here 100486 is the date.


How can I show it as a date 10-04-86 ?


and how to get datevalue for the date? I dont want to change the datatype property standard or date or something.It has to happen through code.I tried it with datevalue(20-05-1982)-gives value error.


Please help.

 

[Debraj]

Hi bee123..


* How can I show it as a date 10-04-86 ?

=TEXT(DATEVALUE(TEXT(LEFT(a1,6),"00-00-00")),"DD-MMM-YYYY")

Change the DD-MMM-YYYY to your required format.


* I tried it with datevalue(20-05-1982)-gives value error.

[code]=DATEVALUE("20-05-1982")

Try to put the value in ("") Quote


* I dont want to change the datatype property standard or date or something.It has to happen through code

=TEXT(DATEVALUE("20-05-1982&#34,"D-M-YY&#34[/code]

 

[SirJB7]

Hi, bee123!


It seems as if the numbers that include the date&time are in the format ddmmyyhhmm, so if your system setting date is in format mm/dd/yy you won't be able to directly use the DATEVALUE function with LEFT function. Instead of DATEVALUE use DATE and instead of LEFT use MID.


10-04-86 stands for April 10th or October 4th? No doubt that 20-05-1982 stands for May 20th. BTW, you have numbers with two year digits and four year digits? If so, you'll need and additional help column to indicate this.


Regards!