Add ordinal suffix to the date like st nd rd th using excel formula

I was asked question about adding suffix like

1st June 2014
2nd June 2014  
3rd June 2014 etc


Suppose Cell A1 is the date then try the below formula to get ordinal suffix


=TEXT(A1,"d")&IFERROR(MID("1st2nd3rd21st22nd23rd31st",FIND(DAY(A1),"1st2nd3rd21st22nd23rd31st")+IF(LEN(DAY(A1))=1,1,2),2),"th")& TEXT(A1," mmm yyyy")

Comments

Post a Comment

Popular posts from this blog

Excel formula on finding the length of numbers, letters, small letters, capital letters and special characters

We know that excel can find length of cell using LEN () function. How about finding Length of letters/Numbers/special characters only? To find the length of capital letters and small letters try the below formula, where A1 is your data =SUMPRODUCT(--ISNUMBER(LOOKUP(CODE(MID(A1,ROW($1:$500),1)),{65,91,97,123},{1,"N",2,"N"}))) Length of Capital letters only =SUMPRODUCT(--ISNUMBER(LOOKUP(CODE(MID(A1,ROW($1:$500),1)),{65,91,97,123},{1,"N","N","N"}))) Length of small letters only =SUMPRODUCT(--ISNUMBER(LOOKUP(CODE(MID(A1,ROW($1:$500),1)),{65,91,97,123},{"N","N",2,"N"}))) Length of numbers used in cell =SUMPRODUCT(--ISNUMBER(LOOKUP(CODE(MID(A1,ROW($1:$500),1)),{48,58},{1,"N"}))) Length of cell apart from letters and numbers which is Special characters =LEN(A1)-SUMPRODUCT(--ISNUMBER(LOOKUP(CODE(MID(A1,ROW($1:$500),1)),{48,58,65,91,97,123},{1,"N",1,"N...
Read more