Get Contract Date (3/14/14 to "14th Day of March, 2014")

[bondra76]

Just need a formula for converting a mm/dd/yyyy date to "14th Day of March, 2014"

included in the formula I need something to convert the 3 to 3rd, 4 to 4th, etc for the days - that's where I am running into the most trouble.

Anything you guys can suggest I would greatly appreciate - thanks!

[AlKey]

Try this one

=DAY(A1)&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)&" Day of "&TEXT(A1,"mmmm, ")&YEAR(A1)

	A	B
1	3/14/2014	14th Day of March, 2014

[bondra76]

Thanks for the help - it's working for all the "th" dates but for 4 it's coming back with 4nd and for 5 it's coming back with 5rd

[AlKey]

Try this adjusted formula

=DAY(A1)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(A1))*(MOD(DAY(A1)-11,100)>2)+1),2)&" Day of "&TEXT($A1,"mmmm, yyyy")

	A	B
1	3/14/2014	14th Day of March, 2014
2	3/15/2014	15th Day of March, 2014
3	3/16/2014	16th Day of March, 2014
4	3/17/2014	17th Day of March, 2014
5	3/18/2014	18th Day of March, 2014
6	3/19/2014	19th Day of March, 2014
7	3/20/2014	20th Day of March, 2014
8	3/21/2014	21st Day of March, 2014
9	3/22/2014	22nd Day of March, 2014
10	3/23/2014	23rd Day of March, 2014
11	3/24/2014	24th Day of March, 2014
12	3/25/2014	25th Day of March, 2014
13	3/26/2014	26th Day of March, 2014
14	3/27/2014	27th Day of March, 2014
15	3/28/2014	28th Day of March, 2014
16	3/29/2014	29th Day of March, 2014
17	3/30/2014	30th Day of March, 2014
18	3/31/2014	31st Day of March, 2014
19	4/1/2014	1st Day of April, 2014
20	4/2/2014	2nd Day of April, 2014
21	4/3/2014	3rd Day of April, 2014

[bondra76]

Thank you so much it worked!