Proble related to date

%3CLINGO-SUB%20id%3D%22lingo-sub-1876528%22%20slang%3D%22en-US%22%3EProble%20related%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1876528%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%3Asmiling_face_with_smiling_eyes%3A%3C%2FP%3E%3CP%3EHow%20to%20change%2011%2F11%2F2020%20to%20%3CSTRONG%3E11th%20November%202020%20%3C%2FSTRONG%3Eor%3CSTRONG%3E%20November%2011th%202020%26nbsp%3B%3C%2FSTRONG%3Ewith%20the%20help%20of%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1876528%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1876778%22%20slang%3D%22en-US%22%3ERE%3A%20Proble%20related%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1876778%22%20slang%3D%22en-US%22%3EOne%20way%20I%20believe%20you%20could%20do%20that%3A%20%3DTEXT(A1%2C%22mmmm%20%22)%26amp%3BDAY(A1)%26amp%3BLOOKUP(DAY(A1)%2C%7B1%2C2%2C3%2C4%2C21%2C22%2C23%2C24%2C31%7D%2C%7B%22st%22%2C%22nd%22%2C%22rd%22%2C%22th%22%2C%22st%22%2C%22nd%22%2C%22rd%22%2C%22th%22%2C%22st%22%7D)%26amp%3B%22%20%22%26amp%3BYEAR(A1)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1876798%22%20slang%3D%22en-US%22%3ERE%3A%20Proble%20related%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1876798%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%20Its%20working.%20Thank%20you%20so%20much%20Sir%3Asmiling_face_with_smiling_eyes%3A%26nbsp%3B%3C%2FP%3E%3CP%3ESir%2C%20can%20you%20explain%20this%20formula%3F%3F%20Please%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello Everyone

How to change 11/11/2020 to 11th November 2020 or November 11th 2020 with the help of formula?

 

3 Replies
Highlighted
One way I believe you could do that: =TEXT(A1,"mmmm ")&DAY(A1)&LOOKUP(DAY(A1),{1,2,3,4,21,22,23,24,31},{"st","nd","rd","th","st","nd","rd","th","st"})&" "&YEAR(A1)
Highlighted

@JMB17  Its working. Thank you so much Sir 

Sir, can you explain this formula?? Please

Highlighted
TEXT(A1,"mmmm ") : formats the date to return only the month in long format (like "November").

DAY(A1) - returns the day as a number.

LOOKUP(DAY(A1),{1,2,3,4,21,22,23,24,31},{"st","nd","rd","th","st","nd","rd","th","st"}) : this part performs a lookup on the day in the first array (the data inside the first pair of braces { } ) and then returns the ordinal abbreviation ("st", "nd", etc.) in the second pair of braces. Lookup will return the largest number that is <= what you're trying to lookup (so 6 matches to 4 - since the days 4-20 all use "th", there's no need to include every day in between 4 and 21).

YEAR(A1) - returns the year as a number.

These parts are then concatenated together using ampersands (&) between them to create the text string.