CONVERT DATE AND MONTH IN ENGLISH IN EXCEL

[Haseeb Alamgir]

Good Info

اظہارِ پسندیدگی کا بہت شکریہ

[syar]

جی اُسی کی بات کر رہا ہوں دوسرے تھریڈ کی

حسیب صاحب اب اس عمر میں محنت نہیں ہوتی اتنی ۔ بہرحال کوڈ کی جگہ فارمولہ استعمال کر لیں۔ اور اپنی مرضی کے مطابق تبدیل کر لیں۔

=CHOOSE(LEFT(TEXT(A1,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--LEFT(TEXT(A1,"000000000.00"))=0,,IF(AND(--MID(TEXT(A1,"000000000.00"),2,1)=0,--MID(TEXT(A1,"000000000.00"),3,1)=0)," Hundred"," Hundred and "))
&CHOOSE(MID(TEXT(A1,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(A1,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(A1,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),
CHOOSE(MID(TEXT(A1,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))
&IF((--LEFT(TEXT(A1,"000000000.00"))+MID(TEXT(A1,"000000000.00"),2,1)+MID(TEXT(A1,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(A1,"000000000.00"),4,1)+MID(TEXT(A1,"000000000.00"),5,1)+MID(TEXT(A1,"000000000.00"),6,1)+MID(TEXT(A1,"000000000.00"),7,1))=0,(--MID(TEXT(A1,"000000000.00"),8,1)+RIGHT(TEXT(A1,"000000000.00")))>0)," Million and "," Million "))
&CHOOSE(MID(TEXT(A1,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--MID(TEXT(A1,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(A1,"000000000.00"),5,1)=0,--MID(TEXT(A1,"000000000.00"),6,1)=0)," Hundred"," Hundred and"))
&CHOOSE(MID(TEXT(A1,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety")
&IF(--MID(TEXT(A1,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(A1,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(A1,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"))
&IF((--MID(TEXT(A1,"000000000.00"),4,1)+MID(TEXT(A1,"000000000.00"),5,1)+MID(TEXT(A1,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(A1,"000000000.00"),7,1)+MID(TEXT(A1,"000000000.00"),8,1)+MID(TEXT(A1,"000000000.00"),9,1))=0,--MID(TEXT(A1,"000000000.00"),7,1)<>0)," Thousand "," Thousand and "))
&CHOOSE(MID(TEXT(A1,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--MID(TEXT(A1,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(A1,"000000000.00"),8,1)=0,--MID(TEXT(A1,"000000000.00"),9,1)=0)," Hundred "," Hundred and "))&
CHOOSE(MID(TEXT(A1,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(A1,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(A1,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(A1,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))

&"  "

2.

=CHOOSE(LEFT(TEXT(A1,"000000000000.00"))+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")&IF(--LEFT(TEXT(A1,"000000000000.00"))=0, ,IF(AND(--MID(TEXT(A1,"000000000000.00"),2,1)=0,--MID(TEXT(A1,"000000000000.00"),3,1)=0),"Hundred ","Hundred and "))&CHOOSE(MID(TEXT(A1,"000000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(A1,"000000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(A1,"000000000000.00"),3,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT(A1,"000000000000.00"),3,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))&IF((--LEFT(TEXT(A1,"000000000000.00"))+MID(TEXT(A1,"000000000000.00"),2,1)+MID(TEXT(A1,"000000000000.00"),3,1))=0, ,IF(AND((--MID(TEXT(A1,"000000000000.00"),4,1)+MID(TEXT(A1,"000000000000.00"),5,1)+MID(TEXT(A1,"000000000000.00"),6,1)+MID(TEXT(A1,"000000000000.00"),7,1))=0,(--MID(TEXT(A1,"000000000000.00"),8,1)+RIGHT(TEXT(A1,"000000000000.00")))>0),"Billion and ","Billion "))&CHOOSE(MID(TEXT(A1,"000000000000.00"),4,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")&IF(--MID(TEXT(A1,"000000000000.00"),4,1)=0, ,IF(AND(--MID(TEXT(A1,"000000000000.00"),5,1)=0,--MID(TEXT(A1,"000000000000.00"),6,1)=0),"Hundred ","Hundred and "))&CHOOSE(MID(TEXT(A1,"000000000000.00"),5,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(A1,"000000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(A1,"000000000000.00"),6,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT(A1,"000000000000.00"),6,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))&IF((--MID(TEXT(A1,"000000000000.00"),4,1)+MID(TEXT(A1,"000000000000.00"),5,1)+MID(TEXT(A1,"000000000000.00"),6,1))=0, ,IF(OR((--MID(TEXT(A1,"000000000000.00"),7,1)+MID(TEXT(A1,"000000000000.00"),8,1)+MID(TEXT(A1,"000000000000.00"),9,1))=0,--MID(TEXT(A1,"000000000000.00"),7,1)>0),"Million ","Million and "))&CHOOSE(MID(TEXT(A1,"000000000000.00"),7,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")&IF(--MID(TEXT(A1,"000000000000.00"),7,1)=0, ,IF(AND(--MID(TEXT(A1,"000000000000.00"),8,1)=0,--MID(TEXT(A1,"000000000000.00"),9,1)=0),"Hundred ","Hundred and "))&CHOOSE(MID(TEXT(A1,"000000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(A1,"000000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(A1,"000000000000.00"),9,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT(A1,"000000000000.00"),9,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))&IF((--MID(TEXT(A1,"000000000000.00"),7,1)+MID(TEXT(A1,"000000000000.00"),8,1)+MID(TEXT(A1,"000000000000.00"),9,1))=0, ,IF(OR((--MID(TEXT(A1,"000000000000.00"),10,1)+MID(TEXT(A1,"000000000000.00"),11,1)+MID(TEXT(A1,"000000000000.00"),12,1))=0,--MID(TEXT(A1,"000000000000.00"),10,1)<>0),"Thousand ","Thousand and "))&CHOOSE(MID(TEXT(A1,"000000000000.00"),10,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")&IF(--MID(TEXT(A1,"000000000000.00"),10,1)=0, ,IF(AND(--MID(TEXT(A1,"000000000000.00"),11,1)=0,--MID(TEXT(A1,"000000000000.00"),12,1)=0),"Hundred ","Hundred and "))&CHOOSE(MID(TEXT(A1,"000000000000.00"),11,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")&IF(--MID(TEXT(A1,"000000000000.00"),11,1)<>1,CHOOSE(MID(TEXT(A1,"000000000000.00"),12,1)+1,,"One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine "),CHOOSE(MID(TEXT(A1,"000000000000.00"),12,1)+1,"Ten ","Eleven ","Twelve ","Thirteen ","Fourteen ","Fifteen ","Sixteen ","Seventeen ","Eighteen ","Nineteen "))&"Point "&CHOOSE(MID(TEXT(A1,"000000000000.00"),14,1)+2,,"zero ","One ","Two ","Three ","Four ","Five ","Six ","Seven ","Eight ","Nine ")&CHOOSE(MID(TEXT(A1,"000000000000.00"),15,1)+2,,"zero","One","Two","Three","Four","Five","Six","Seven","Eight","Nine")

3.

="PKR "&CHOOSE(LEFT(TEXT(A1,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--LEFT(TEXT(A1,"000000000.00"))=0,,IF(AND(--MID(TEXT(A1,"000000000.00"),2,1)=0,--MID(TEXT(A1,"000000000.00"),3,1)=0)," Hundred"," Hundred and "))
&CHOOSE(MID(TEXT(A1,"000000000.00"),2,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(A1,"000000000.00"),2,1)<>1,CHOOSE(MID(TEXT(A1,"000000000.00"),3,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),
CHOOSE(MID(TEXT(A1,"000000000.00"),3,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))
&IF((--LEFT(TEXT(A1,"000000000.00"))+MID(TEXT(A1,"000000000.00"),2,1)+MID(TEXT(A1,"000000000.00"),3,1))=0,,IF(AND((--MID(TEXT(A1,"000000000.00"),4,1)+MID(TEXT(A1,"000000000.00"),5,1)+MID(TEXT(A1,"000000000.00"),6,1)+MID(TEXT(A1,"000000000.00"),7,1))=0,(--MID(TEXT(A1,"000000000.00"),8,1)+RIGHT(TEXT(A1,"000000000.00")))>0)," Million and "," Million "))
&CHOOSE(MID(TEXT(A1,"000000000.00"),4,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--MID(TEXT(A1,"000000000.00"),4,1)=0,,IF(AND(--MID(TEXT(A1,"000000000.00"),5,1)=0,--MID(TEXT(A1,"000000000.00"),6,1)=0)," Hundred"," Hundred and"))
&CHOOSE(MID(TEXT(A1,"000000000.00"),5,1)+1,,," Twenty"," Thirty"," Forty"," Fifty"," Sixty"," Seventy"," Eighty"," Ninety")
&IF(--MID(TEXT(A1,"000000000.00"),5,1)<>1,CHOOSE(MID(TEXT(A1,"000000000.00"),6,1)+1,," One"," Two"," Three"," Four"," Five"," Six"," Seven"," Eight"," Nine"),CHOOSE(MID(TEXT(A1,"000000000.00"),6,1)+1," Ten"," Eleven"," Twelve"," Thirteen"," Fourteen"," Fifteen"," Sixteen"," Seventeen"," Eighteen"," Nineteen"))
&IF((--MID(TEXT(A1,"000000000.00"),4,1)+MID(TEXT(A1,"000000000.00"),5,1)+MID(TEXT(A1,"000000000.00"),6,1))=0,,IF(OR((--MID(TEXT(A1,"000000000.00"),7,1)+MID(TEXT(A1,"000000000.00"),8,1)+MID(TEXT(A1,"000000000.00"),9,1))=0,--MID(TEXT(A1,"000000000.00"),7,1)<>0)," Thousand "," Thousand and "))
&CHOOSE(MID(TEXT(A1,"000000000.00"),7,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")
&IF(--MID(TEXT(A1,"000000000.00"),7,1)=0,,IF(AND(--MID(TEXT(A1,"000000000.00"),8,1)=0,--MID(TEXT(A1,"000000000.00"),9,1)=0)," Hundred "," Hundred and "))&
CHOOSE(MID(TEXT(A1,"000000000.00"),8,1)+1,,,"Twenty ","Thirty ","Forty ","Fifty ","Sixty ","Seventy ","Eighty ","Ninety ")
&IF(--MID(TEXT(A1,"000000000.00"),8,1)<>1,CHOOSE(MID(TEXT(A1,"000000000.00"),9,1)+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"),CHOOSE(MID(TEXT(A1,"000000000.00"),9,1)+1,"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"))

&IF(RIGHT(TEXT(A1,"000000000.00"),2)>"000000000.00"," & "&RIGHT(TEXT(A1,"000000000.00"),2)&" Paise Only."," Only.")

4.

=LET(Denom, {" Million ";" Thousand ";"  ";" Paisas"},Nums,{"","One","Two","Three","Four","Five","Six","Seven","Eight"," Nine"},
Teens,{"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},Tens,{"","Ten","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"},grp, {0;1;2;3},LET(N, SUBSTITUTE( TEXT( A1, REPT(0,9)&".00" ),".","0"),H, VALUE( MID( N, 3*grp+1, 1) ), T, VALUE( MID( N, 3*grp+2, 1) ),U, VALUE( MID( N, 3*grp+3, 1) ),Htxt, IF( H, INDEX( Nums, H+1 ) & " Hundred ", "" ),Ttxt, IF( T>1, INDEX( Tens, T+1 ) & IF( U>0, "-", "" ), " " ),
Utxt, IF( (T+U), IF( T=1, INDEX( Teens, U+1 ), INDEX(Nums, U+1 ) ),"" ),CONCAT( IF( H+T+U,Htxt & Ttxt & Utxt & Denom, "" ) )))

Please change ranges/reference accordingly.

"

صرف جنرل نالج کے لیے یہاں پیش کر رہا ہوں۔ پہلے سے تیار پڑے تھے میرے پاس۔

[Haseeb Alamgir]

صرف جنرل نالج کے لیے یہاں پیش کر رہا ہوں۔ پہلے سے تیار پڑے تھے میرے پاس۔
سر جی بس اس بالی عمر میں کام ہونا چاہیے
-۔-۔-۔
کسی ایک کوڈ کو استعمال کر لیا جائے
یا
سب مل کر اس کام کو سرانجام دیں گے؟
آپ کا بہت مشکور رہونگا
ضرور ٹرائی کرتےہیں اس کو
پھر تفصیلی ریپلائی دیتے ہیں
مگر
ابھی تک کے لئے بہت بہت شکریہ

[Haseeb Alamgir]

صرف جنرل نالج کے لیے یہاں پیش کر رہا ہوں۔ پہلے سے تیار پڑے تھے میرے پاس۔

اؤٹ پٹ یہ آرہا ہے
دیکھیں کیا ہم نے آپ کے دیے ہوئے کوڈ کو درست استعمال کیا ہے؟

[syar]

اؤٹ پٹ یہ آرہا ہے
دیکھیں کیا ہم نے آپ کے دیے ہوئے کوڈ کو درست استعمال کیا ہے؟

G han. sahe use keya ha. formula taqreban same same ha bas kam thora mukhtalif krta ha. aap ko jo output pasand ho usy use kar lain.
ya aap bar bar use krna chahty hain tu isko name ranges m define kar lain aik bar aur phir pori sheet m jahan chahen use krain.
ya in formulas ko kahen likh kar rakh lain.bad m kam aay ga. point k bad waly hindsy ko adjust krty jain apni marzi sa.

Formula thora asan ha sab k leye q k VB par har koi kam naee kar sakta. office 365 m developer ya VB k option thory change kr deye gay hain. Formula is leye share keya tha k sab ko asani ho aur use kar sakain

[Haseeb Alamgir]

G han. sahe use keya ha. formula taqreban same same ha bas kam thora mukhtalif krta ha. aap ko jo output pasand ho usy use kar lain.
ya aap bar bar use krna chahty hain tu isko name ranges m define kar lain aik bar aur phir pori sheet m jahan chahen use krain.
ya in formulas ko kahen likh kar rakh lain.bad m kam aay ga. point k bad waly hindsy ko adjust krty jain apni marzi sa.

Formula thora asan ha sab k leye q k VB par har koi kam naee kar sakta. office 365 m developer ya VB k option thory change kr deye gay hain. Formula is leye share keya tha k sab ko asani ho aur use kar sakain

بہت شکریہ ۔ میں نے اس فارمولے کو آفس 365 پر ہی چیک کیا ہے

[Haseeb Alamgir]

اس کو مذید کام میں لانے کے لیے سوچتے ہیں
یہ بھی امید کی جاتی ہے کہ یہ تھریڈ دوستوں کے بہت کام آنے والا ہے
اگر دلچسپی لیں تو
سیکھنا ہے تو شرمانا کیسا

[Haseeb Alamgir]

G han. sahe use keya ha.

اگر ہم اس کا ایڈ ان بنا لیں تو کیسا رہے گا؟

[syar]

اگر ہم اس کا ایڈ ان بنا لیں تو کیسا رہے گا؟

G sir, bilkul bana lain

[Haseeb Alamgir]

G sir, bilkul bana lain

اجازت دینے کا بہت شکریہ پھر اس کو استعمال کرنےمیں زیادہ آسانی ہوگی