How Do I Preserve 'Long Date' Formatting When Using 'Generate Documents'?

Dylan Welch

I'm in the process of attempting to build a document to be generated from our Smartsheet and am trying to pull in a date field. I have the date column on the Smartsheet set to a long date (i.e. March 16, 2021), but it is coming in on the form as 03/16/2021.

I have also double checked the fillable pdf I am using and tried changing the settings on the field from a text field with date formatting (mmmm, d, yyyy) to one with no formatting at all, and neither way is making any difference.

Any suggestions/workarounds?

Genevieve P.

Hi @Dylan Welch

Currently Document Builder maps dates based on the user's regional preferences in Smartsheet (see: Specify Default Date and Number Formats With Regional Preferences).

Please fill out the feedback form, here, to let the Product Team know that Document Builder being able to map the long-date format is a feature that would be useful for you.

Thank you!

Genevieve

Jacquie Stephenson

I'm having the same challenge, I could save hundreds of hours of work across our business creating certificates if I could get this to work, please help!

Andrée Starå

Hi @Jacquie Stephenson

I hope you're well and safe!

Would it work to use a 3rd party option, or do you want to wait until it works in Document Builder?

Also, have you tried adding a so-called helper column to make it text and reference that column instead of in Document Builder?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Hoang Le

hello @Andrée Starå

that's a nice idea, could you please share the formula for the helper column to change DD-MMM-YY (date format) to DD-MMM-YY (text format)

Andrée Starå

Hi @Hoang Le

I hope you're well and safe!

How did it go? Did you manage to get something set up?

I hope that helps!

Have a fantastic weekend & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Dylan Welch

@Hoang Le I ended up adding another text column right next to the date column and just entering the date twice, once into the date column to actually capture the date, and another text column to capture the long form date. On the form, I end up referencing one or the other, depending on what is needed. It's a bit clunky of a workaround, but it works!

Maysa Bakir

I put a text field next to the date column and used this formula to turn the format into DD-MMM-YYYY and now it works. "[Syn Due Date (RM to QC)]" = the column with the date that I am trying to reformat. Looks at the first 2 numbers and sees which month it is and then add the day and year by returning the next 2 values after each slash.

=IF(MONTH([Syn Due Date (RM to QC)]@row) = 1, MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1, 2) + "-" + "Jan" + "-20" + MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1) + 1, 2), IF(MONTH([Syn Due Date (RM to QC)]@row) = 2, MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1, 2) + "-" + "Feb" + "-20" + MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1) + 1, 2), IF(MONTH([Syn Due Date (RM to QC)]@row) = 3, MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1, 2) + "-" + "Mar" + "-20" + MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1) + 1, 2), IF(MONTH([Syn Due Date (RM to QC)]@row) = 4, MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1, 2) + "-" + "Apr" + "-20" + MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1) + 1, 2), IF(MONTH([Syn Due Date (RM to QC)]@row) = 5, MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1, 2) + "-" + "May" + "-20" + MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1) + 1, 2), IF(MONTH([Syn Due Date (RM to QC)]@row) = 6, MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1, 2) + "-" + "Jun" + "-20" + MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1) + 1, 2), IF(MONTH([Syn Due Date (RM to QC)]@row) = 7, MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1, 2) + "-" + "Jul" + "-20" + MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1) + 1, 2), IF(MONTH([Syn Due Date (RM to QC)]@row) = 8, MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1, 2) + "-" + "Aug" + "-20" + MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1) + 1, 2), IF(MONTH([Syn Due Date (RM to QC)]@row) = 9, MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1, 2) + "-" + "Sep" + "-20" + MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1) + 1, 2), IF(MONTH([Syn Due Date (RM to QC)]@row) = 10, MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1, 2) + "-" + "Oct" + "-20" + MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1) + 1, 2), IF(MONTH([Syn Due Date (RM to QC)]@row) = 11, MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1, 2) + "-" + "Nov" + "-20" + MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1) + 1, 2), IF(MONTH([Syn Due Date (RM to QC)]@row) = 12, MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1, 2) + "-" + "Dec" + "-20" + MID([Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row, FIND("/", [Syn Due Date (RM to QC)]@row) + 1) + 1, 2)))))))))))))