xlf QandA series

Adding an ordinal indicator to the TEXT function date


QUESTION

CAN YOU SHOW ME HOW TO INCLUDE THE st, nd, rd, th INDICATORS IN A TEXT FUNCTION DATE


Answer icon Answer


Include an array constant of the form {1,"st";2,"nd";3,"rd";4,"th";21,"st";22,"nd";23,"rd";24,"th";31,"st"}, and use this as the range_lookup argument to a VLOOKUP function. See the WS formula box in point 2 below.


Suppose that TODAY is 3 December 2018 then:

  1. ="Today is: " & TEXT(TODAY(), dddd, d mmmm yyyy) returns Monday, 3 December 2018
  2. To include the rd ordinal indicator on the 3, ie. 3rd, the WS formula is:
  3. ="Today is: " & TEXT(TODAY(), "dddd, d""" & VLOOKUP(DAY(TODAY()),{1,"st";2,"nd";3,"rd";4,"th";21,"st";22,"nd";23,"rd";24,"th";31,"st"},2) & """ mmmm yyyy")
     
  4. DAY(TODAY()) returns 3
  5. VLOOKUP(3,{1,"st";2,"nd";3,"rd";4,"th";21,"st";22,"nd";23,"rd";24,"th";31,"st"},2) returns "rd" (a text string)
  6. "dddd, d""" & "rd" & """ mmmm yyyy" (figure 1 formula evaluation step 5) [returns] "dddd, d""rd"& """ mmmm yyyy" (formula evaluation step 6 removes the first ampersand " & ") [returns] "dddd, d""rd"" mmmm yyyy" (figure 1 formula evaluation step 7 removes the second ampersand " & ")
  7. xlf-ordinal-indicator-5-s5-s7
    Fig 1: Evaluate Formula dialog box - step 5 and step 7
  8. The WS view of formula and return value (figure 2 - cell R14C3)
    xlf-date-ordinal
    Fig 2: Date ordinal indicator - displaying the 3rd