Forum Discussion

Daniel Azzopardi's avatar
Daniel Azzopardi
Copper Contributor
Sep 06, 2018

Date formats with a suffix

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.

excel

6 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 06, 2018

    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.

    • Daniel Azzopardi's avatar
      Daniel Azzopardi
      Copper Contributor
      Sep 06, 2018

      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:

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 06, 2018

        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)

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 06, 2018

    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

     

Resources