0

Title explains it. I need to know how to create a custom number format in which a cell would display only the date and suffix of a full date entered into the cell example:1/05/2019 -> 5th

I have accomplished this myself but I have to manually determine which day has which suffix and then add the suffix to the custom number format manually. Not a big deal but if this is possible, it would save some time.

This is what I am using as the custom number format for the -th suffix: d"th". This works but there has to be a way to do it as I described above.

  • 1
    this can't be done via internal formatting: Format > Number > Custom date – user0 Jan 11 at 1:32
0

The Google Sheets API documentation includes an article about Date and Number formats. According to this article, the default conditions that could be used are for negative, positive, and zero . They could be customized to something like low, high and in between but in this case, adding ordinal suffixes, this isn't enough as the rules are more complex.

The alternative to applying the date format manually is to use an script / add-on that applies a custom format according to the rules:

Integer rightmost digits:

  • 0, 4 to 9, 11 to 13: th
  • 1: st
  • 2: nd
  • 3: rd

Reference

-1

=DAY(A1)&LOOKUP(DAY(A1),
 { 1  , 2  , 3  , 4  , 21 , 22 , 23 , 24 , 31 },
 {"st","nd","rd","th","st","nd","rd","th","st"})

=ARRAYFORMULA(IF(LEN(A1:A), DAY(A1:A)&LOOKUP(DAY(A1:A),
 { 1  , 2  , 3  , 4  , 21 , 22 , 23 , 24 , 31 },
 {"st","nd","rd","th","st","nd","rd","th","st"}), ))
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.