# Thread: CONVERT DATE AND MONTH IN ENGLISH IN EXCEL

1. loyal_cupid said:
Good Info
اظہارِ پسندیدگی کا بہت شکریہ

Last Online
Yesterday @ 04:01 PM
Join Date
31 Jul 2009
Location
Nowshera(KP)
Age
45
Gender
Male
Posts
852
9
Credits
375
Thanked
340
Haseeb Alamgir said:

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

Code:
```=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.
Code:
`=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.
Code:
```="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.
Code:
```=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, "" ) )))```

"

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

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

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

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

Last Online
Yesterday @ 04:01 PM
Join Date
31 Jul 2009
Location
Nowshera(KP)
Age
45
Gender
Male
Posts
852
9
Credits
375
Thanked
340
Haseeb Alamgir said:

اؤٹ پٹ یہ آرہا ہے
دیکھیں کیا ہم نے آپ کے دیے ہوئے کوڈ کو درست استعمال کیا ہے؟
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

6. syar said:
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 پر ہی چیک کیا ہے

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

8. syar said:
G han. sahe use keya ha.
اگر ہم اس کا ایڈ ان بنا لیں تو کیسا رہے گا؟

Last Online
Yesterday @ 04:01 PM
Join Date
31 Jul 2009
Location
Nowshera(KP)
Age
45
Gender
Male
Posts
852
9
Credits
375
Thanked
340
Haseeb Alamgir said:

اگر ہم اس کا ایڈ ان بنا لیں تو کیسا رہے گا؟
G sir, bilkul bana lain

10. syar said:
G sir, bilkul bana lain
اجازت دینے کا بہت شکریہ پھر اس کو استعمال کرنےمیں زیادہ آسانی ہوگی

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•