Date Help

Nicole_Codi17 · 02/08/24

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

Chris Mondeau · 02/09/24

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!

Chris Mondeau · 02/09/24