# Date Help

Options

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

• ✭✭✭✭✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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!

• Options

Brilliant!!! Thank you

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!