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.

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

=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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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