Using excel 2016, tried every formula and macros I could find, but it still doesn't work.
I have a date format
11.11.2020 11:09:18
I need it to be like this
Nov 11th, 2020 , 11:09 AM
The main problem is with ending after date: 1st, 2nd, 3rd, 4th etc.
P.s. each date is in one cell
Maybe somebody had a similar problem and managed to solve it.
You could try the following:
Formula in B1:
=TEXT(A1,"[$-409]mmm d")&IFERROR(INDEX({"st","nd","rd"},ROUNDUP(MATCH(DAY(A1),{1,21,31,2,22,32,3,23},0)/3,0)),"th")&TEXT(A1," yyyy hh:mm AM/PM")
And if you want the commas in there as well:
Formula in C1:
=SUBSTITUTE(TEXT(A1,"[$-409]mmm d, yyyy, hh:mm AM/PM"),",",IFERROR(INDEX({"st,","nd,","rd,"},ROUNDUP(MATCH(DAY(A1),{1,21,31,2,22,32,3,23},0)/3,0)),"th,"),1)
Mind you, this transform a data into text!