Date formats with a suffix

[Daniel Azzopardi]

I have an excel sheet where I will be entering dates as I go, all within the same column. I would like these dates to be automatically formatted into full dates with a suffix(ie. 08/18 would be 1st August 2018, 3/8/18 would be 3rd August 2018). 

I have come as far as to creating the custom formats d"st/nd/rd/th" mmmm yyy but since you can only have one format selected for the whole column at a time I had to resort to Conditional Formatting. I am using the formula below with their respective date format but it is still not working.

=OR(DAY(D7:D1000)=1,DAY(D7:D1000)=21,DAY(D7:D1000)=31) with format d"st" mmmm yyy

=OR(DAY(D7:D1000)=2,DAY(D7:1000)=22) with format d"nd" mmmm yyy

=OR(DAY(D7:D1000)=3,DAY(D7:D1000)=23) with format d"rd" mmmm yyy

=OR(DAY(D1:D1000)=4,DAY(D7:D1000)=5,DAY(D7:D1000)=6,DAY(D7:D1000)=7,DAY(D7:D1000)=8,DAY(D7:D1000)=9,DAY(D7:D1000)=10,DAY(D7:D1000)=11,DAY(D7:D1000)=12,DAY(D7:D1000)=13,DAY(D7:D1000)=14,DAY(D7:D1000)=15,DAY(D7:D1000)=16,DAY(D7:D1000)=17,DAY(D7:D1000)=18,DAY(D7:D1000)=19,DAY(D7:D1000)=20,DAY(D7:D1000)=24,DAY(D7:D1000)=25,DAY(D7:D1000)=26,DAY(D7:D1000)=27,DAY(D7:D1000)=28,DAY(D7:D1000)=29,DAY(D7:D1000)=30) with format d"th" mmmm yyy

I cannot find where the mistake is so I am guessing my formula are completely wrong. Any suggestions would be greatly appreciated.

[#2]

Hi Daniel,

For the rule formula use

=OR(DAY($D7)=1,DAY($D7)=21,DAY($D7)=31)

etc., and apply the rule to your entire range

[#3]

By the way, you don't need 4th rule. Apply custom format d"th" mmmm yyy to your entire range and after that add three conditional formatting rules for 1st, 2nd and 3rd

Attached.

[#4]

Thank you for your reply. Unfortunately, I still seem to be doing something wrong. I set the formatting for the whole column as you suggested and then set the rules separately for st, nd and rd but only the main formatting is happening. 

Screenshot below of what I end up with:

[#5]

Since you apply the rule to entire column D in the formulas it shall be D1 instead of D7 (first cell of the range to which you apply the rule)

[#6]

Thank you so much! Works exactly as I expected it to.

[#7]

You are welcome