Date Help

I am using Document Generator to create contracts The day needs to be reflected as the following:

is there a way to have days with nd,th,st i.e 22nd like the example above?


Thank you

Best Answer

  • Chris Mondeau
    Chris Mondeau ✭✭✭✭✭✭
    edited 02/09/24 Answer ✓

    Hi @Nicole_Codi17

    Assuming something like this:

    Day: =DAY(Date@row)

    Suffix: =IF(OR(Day@row = 11, Day@row = 12, Day@row = 13), "th", IF(MOD(Day@row, 10) = 1, "st", IF(MOD(Day@row, 10) = 2, "nd", IF(MOD(Day@row, 10) = 3, "rd", "th"))))

    Month: =IF(MONTH(Date@row) = 1, "January", IF(MONTH(Date@row) = 2, "February", IF(MONTH(Date@row) = 3, "March", IF(MONTH(Date@row) = 4, "April", IF(MONTH(Date@row) = 5, "May", IF(MONTH(Date@row) = 6, "June", IF(MONTH(Date@row) = 7, "July", IF(MONTH(Date@row) = 8, "August", IF(MONTH(Date@row) = 9, "September", IF(MONTH(Date@row) = 10, "October", IF(MONTH(Date@row) = 11, "November", IF(MONTH(Date@row) = 12, "December"))))))))))))

    Year: =YEAR(Date@row)

    Formatted: =Day@row + Suffix@row + " day of " + Month@row + " " + Year@row

    I've split it out to showcase what's going on behind scenes. A lot of the logic comes from the suffix formula, which uses the MOD() function to return the last integer from the day value, and then returns the appropriate suffix. There's an exception for the 11, 12, and 13 days of the month which is captured in the initial if(or()).

    For the month, this is formula referenced in the community formula sheet that just matches the appropriate name of the month it it's numeric equivalent.

    And finally I just append all those together in the Formatted column to put in the layout desired.

    Hope this helps!

Answers

  • Chris Mondeau
    Chris Mondeau ✭✭✭✭✭✭
    edited 02/09/24 Answer ✓

    Hi @Nicole_Codi17

    Assuming something like this:

    Day: =DAY(Date@row)

    Suffix: =IF(OR(Day@row = 11, Day@row = 12, Day@row = 13), "th", IF(MOD(Day@row, 10) = 1, "st", IF(MOD(Day@row, 10) = 2, "nd", IF(MOD(Day@row, 10) = 3, "rd", "th"))))

    Month: =IF(MONTH(Date@row) = 1, "January", IF(MONTH(Date@row) = 2, "February", IF(MONTH(Date@row) = 3, "March", IF(MONTH(Date@row) = 4, "April", IF(MONTH(Date@row) = 5, "May", IF(MONTH(Date@row) = 6, "June", IF(MONTH(Date@row) = 7, "July", IF(MONTH(Date@row) = 8, "August", IF(MONTH(Date@row) = 9, "September", IF(MONTH(Date@row) = 10, "October", IF(MONTH(Date@row) = 11, "November", IF(MONTH(Date@row) = 12, "December"))))))))))))

    Year: =YEAR(Date@row)

    Formatted: =Day@row + Suffix@row + " day of " + Month@row + " " + Year@row

    I've split it out to showcase what's going on behind scenes. A lot of the logic comes from the suffix formula, which uses the MOD() function to return the last integer from the day value, and then returns the appropriate suffix. There's an exception for the 11, 12, and 13 days of the month which is captured in the initial if(or()).

    For the month, this is formula referenced in the community formula sheet that just matches the appropriate name of the month it it's numeric equivalent.

    And finally I just append all those together in the Formatted column to put in the layout desired.

    Hope this helps!

  • Brilliant!!! Thank you

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!