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
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:
="Today is: " & TEXT(TODAY(), dddd, d mmmm yyyy)
returns Monday, 3 December 2018- To include the rd ordinal indicator on the 3, ie. 3rd, the WS formula is:
DAY(TODAY())
returns 3VLOOKUP(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)"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 " & ")Fig 1: Evaluate Formula dialog box - step 5 and step 7 - The WS view of formula and return value (figure 2 - cell R14C3)
Fig 2: Date ordinal indicator - displaying the 3rd
="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")
- Download the file: xlf-dollar-to-euro-plus-ordinal-day [52 KB]
- Development platform: Excel Office 365 ProPlus 64 bit Version 1811
- Published: 3rd December 2018
- Revised: Friday 24th of February 2023 - 02:39 PM, Pacific Time (PT)