# Proble related to date

Regular Contributor

# Proble related to date

Hello Everyone

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

3 Replies

# RE: Proble related to date

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)

# RE: Proble related to date

@JMB17  Its working. Thank you so much Sir

Sir, can you explain this formula?? Please

# RE: Proble related to date

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.