Date of Birth to word conversion

[Ganeszgk]

Respected sir/Madam,
[FONT=&quot]I have large number database of the Date of Birth 23/03/1985. The dob display another cell
[Twenty Three - March - Nineteen Fifty Eight].
For example:
A1: 28.8.1987
A2:[Twenty Eight-August-Nineteen Eighty Seven] this method want i.

How it work, please send coding will having and please to mention step by step process. Thank you..
i hoping...



[/FONT]
[FONT=&quot]<ins class="adsbygoogle sfadslot_1" data-language="en" data-ad-client="pub-3295671961621260" data-ad-slot="9304159522" data-adsbygoogle-status="done" style="box-sizing: inherit; background: none; color: rgb(0, 0, 0); text-decoration: none; min-width: 320px; max-width: 1200px; width: 540px; height: 220px; display: inline-block;"><ins id="aswift_1_expand" style="box-sizing: inherit; background: transparent; text-decoration: none; display: inline-table; border: none; height: 220px; margin: 0px; padding: 0px; position: relative; visibility: visible; width: 540px;"></ins></ins>
[/FONT]

 

[Ganeszgk]

make a new spreadsheet and put 25/02/2017 in A1
in B1 put =text(A1,"dd/mm/yyyy")

tell me what you get in B1

I will send you a spreadsheet but not until this post is deemed closed, because that is the rules of this forum.

Respected Sir, am trying this formula its showing

A1 25/02/2017

B1 25/02/2017 using this formula =textA1,"dd/mm/yyyy")



Respected sir/Madam,
I have large number database of the Date of Birth 23/03/1985. The dob display another cell
[Twenty Three - March - Nineteen Fifty Eight].

For example:

A1: 28/8/1987
A2:[Twenty Eight-August-Nineteen Eighty Seven]

this want i.

 

[Ganeszgk]

column 1 is the date as a normal date
column 2 is the date in text format
cols 3,4,5 is the text format split into day month year
then we lookup the day (28) in the daytable and it returns "twentyeighth"
likewise "August" and "1987"
I then used concatenate to get them all together as text

Does that help ?

No sir, it's also cant showing result.,
am tried like this format please see this one..

column
A1 28/08/1987
B1 twentyeighth August Nineteen Eighty Seven
C1 first ....to.... thirtyfirst
D1 January ...to... December
E1 Nineteen Eighty ..to.. Two Thousand Ten
F1 =CONCATENATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITU TE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTIT UTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A 1,FIND(".",A1)-1),"31","Thirty One"),"30","Thirty"),"29","Twenty Nine"),"28","Twenty Eight"),"27","Twenty Seven"),"26","Twenty Six"),"25","Twenty Five"),"24","Twenty Four"),"23","Twenty Three"),"22","Twenty Two"),"21","Twenty One"),"20","Twenty"),"19","Nineteen"),"18","Eighteen"),"17","Seventeen"),"16","Sixteen"),"15","Fifteen"),"14","Fourteen"),"13","Thirteen"),"12","Twelve"),"11","Eleven"),"10","Ten"),"9","Nine"),"8","Eight"),"7","Seven"),"6","Six"),"5","Five"),"4","Four"),"3","Three"),"2","Two"),"1","One")," - ",TEXT(INT(LEFT(MID(A1,FIND(".",A1)+1,3),FIND(".",MID(A1,FIND(".",A1)+1,3))-1)),"mmmm")," - ",SUBSTITUTE(SUBSTITUTE(MID(MID(A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+1,2),"20","Two Thousand"),"19","Nineteen Hundred")," ",IF(MID(MID(A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+3,1)="1",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(MID(A 1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+3,2),"19","Nineteen"),"18","Eighteen"),"17","Seventeen"),"16","Sixteen"),"15","Fifteen"),"14","Fourteen"),"13","Thirteen"),"12","Twelve"),"11","Eleven"),"10","Ten"),IF(MID(MID(A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+3,1)="0",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(MID(A 1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+3,2),"09","Nine"),"08","Eight"),"07","Seven"),"06","Six"),"05","Five"),"04","Four"),"03","Three"),"02","Two"),"01","One"),"00",""),CONCATENATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(MID (A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+3,1),"9","Ninety "),"8","Eighty "),"7","Seventy "),"6","Sixty "),"5","Fifty "),"4","Fourty "),"3","Thirty "),"2","Twenty "),"0",""),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(MID( A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+4,1),"0",""),"1","One"),"2","Two"),"3","Three"),"4","Four"),"5","Five"),"6","Six"),"7","Seven"),"8","Eight"),"9","Nine")))))

using this formulla but not showing result.. am so sad..

 

[mjbeam]

Respected sir,this is show error on this #Value!

If you enter a date in this format, for example:

23.7.1984

And paste that formula in cell A1, do you get an error?

 

[mjbeam]

One more try:

=CONCATENATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(A1, "dd"),"31","Thirty One"),"30","Thirty"),"29","Twenty Nine"),"28","Twenty Eight"),"27","Twenty Seven"),"26","Twenty Six"),"25","Twenty Five"),"24","Twenty Four"),"23","Twenty Three"),"22","Twenty Two"),"21","Twenty One"),"20","Twenty"),"19","Nineteen"),"18","Eighteen"),"17","Seventeen"),"16","Sixteen"),"15","Fifteen"),"14","Fourteen"),"13","Thirteen"),"12","Twelve"),"11","Eleven"),"10","Ten"),"9","Nine"),"8","Eight"),"7","Seven"),"6","Six"),"5","Five"),"4","Four"),"3","Three"),"2","Two"),"1","One")," - ",TEXT(A1,"mmmm")," - ",SUBSTITUTE(SUBSTITUTE(LEFT(TEXT(A1,"yyyy"),2),"20","Two Thousand"),"19","Nineteen Hundred")," ",IF(MID(TEXT(A1,"yyyy"),3,1)="1",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(TEXT(A1,"yyyy"),3,2),"19","Nineteen"),"18","Eighteen"),"17","Seventeen"),"16","Sixteen"),"15","Fifteen"),"14","Fourteen"),"13","Thirteen"),"12","Twelve"),"11","Eleven"),"10","Ten"),IF(MID(TEXT(A1,"yyyy"),3,1)="0",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(TEXT(A1,"yyyy"),3,2),"09","Nine"),"08","Eight"),"07","Seven"),"06","Six"),"05","Five"),"04","Four"),"03","Three"),"02","Two"),"01","One"),"00",""),CONCATENATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(TEXT(A1,"yyyy"),3,1),"9","Ninety "),"8","Eighty "),"7","Seventy "),"6","Sixty "),"5","Fifty "),"4","Fourty "),"3","Thirty "),"2","Twenty "),"0",""),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(TEXT(A1,"yyyy"),4,1),"0",""),"1","One"),"2","Two"),"3","Three"),"4","Four"),"5","Five"),"6","Six"),"7","Seven"),"8","Eight"),"9","Nine")))))