Document Builder - date format?

Robin Sugiura

I am trying to generate certificates of completion with document builder and include the date of conferral. No matter how I enter the date into a date field (my preference is "Friday, January 1, 2021" as the format), it always enters in the 1/1/21 format. Any ideas for getting document builder to fill it correctly?

Robin Sugiura

I figured out a workaround -- if the date is entered in long form in a text column instead of a date column, it populates as entered.

Don't know if there is another way to affect the formatting if populating from a date column.

Robin Sugiura

I figured out a workaround -- if the date is entered in long form in a text column instead of a date column, it populates as entered.

Don't know if there is another way to affect the formatting if populating from a date column.

Andrée Starå

Hi @Robin Sugiura

Excellent!

Glad you got it working, and thanks for sharing the workaround.

Be safe and have a fantastic week!

Best,

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

✅Please help the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Emma McCannan

I am struggling with the same issue listed in this thread, however my problem is that I cannot add the date as a text because there are other columns that automatically calculate from the date column. Can I somehow copy the date into a text column without running into a formatting issue?

Robin Sugiura

Yes, I had to add a helper column and have to add the long form version manually. It's a crap workaround.

Alexis Kokkinos

Hi All. I had a similar problem in that I wanted to display the modified date in long format. I solved this problem by building a sheet with months (column 1 has numbers 1 to 12 and column 2 has the month name). I then used a new column in my sheet which had the following formula =DAY(Modified@row) + " " + VLOOKUP(MONTH(Modified@row), {Months Range 1}, 2, false) + " " + YEAR(Modified@row). This returns text in long format and i use that in my document builder. Its pretty clean and easy to use. Would be nicer if we could simply format in "long" though :)

Christine Hayner

I find the document builder extremely frustrating from a formatting perspective, this being one of them. The workarounds are not consistent with what they are presenting the features to be - its much less simple that its trying to seem. My formatting is coming through is various stretched out sized, text cut-off in the form field, etc. There needs to be a formatting edit feature that affects the document only, and not needing the source sheet to have the same format (text alignment, font size, etc)

SueinSpain

I was having date format problems and found it was because the format is taken from the PDF file not the smartsheet. So I did a custom date format in my adobe fillable pdf and yippee it worked.

I haven't ried all variations or the very long date but it did make me aware that the PDF formatting overrides the smartsheet formatting

Hope this helps :)

Nicolas Michel

@SueinSpain I'm interested in which date format you used. I've tried what you mentioned. I modified the fields in the pdf to date DD-MMM-YYYY (same as my column date format in the sheet), remapped it but the form generated was still showing the date in DD/MM/YY (in Europe) and MM/DD/YY for my US colleague.😕 It looks like the date regional settings is the default format used for the date column when generating a document.

Kirstine

Mine is creating the document with American format even though I'm in the UK. 😩

Can you help @Andrée Starå ?

Andrée Starå

Hi @Kirstine

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

Kirstine

Hi @Andrée Starå

I still can't get it to do a long date, but I did a ridiculous workaround with three helper columns which at least brings it up in English format. (I needed mine entered at the end of the sentence so I had to add a full stop/period afterwards.)

Date_helper1: =([Start Date]@row) + " "

Date_helper2: ="."

Date_helper3: =LEFT([Date_helper1]@row, 8) + [Date_helper2]@row

Genevieve Van Buhler

I have updated both the PDF format to be mmmm dd, yyyy and changed the smartsheet format to be long format and the date in the document generator is still showing up mm/dd/yy

damon.tackett

Shocked that this is still a problem. Smartsheet is great in so many ways, but the fact that taking information from the date column and transferring it into the document builder using the format already set in the source column is quite simply astonishing. This has been a known issue for what...three years now? Any other column seems to pull in exactly as it is sourced.

I saw somewhere else it is based on your profile preference but after looking through countless regional preferences I did not see one that even was set as MM/DD/YYYY. Come on guys.....if this is how the formatting is based then add ONE that uses that format. Better still add one that uses MM/DD/YYYY and another that uses Month/Day/Year (ex January 01, 2023).

Rebecca Ortinez

Here's a solution. You have to make a separate Smartsheet that has the month numbers and corresponding spelled out month names AND you have to have a year column with the 2 digit year and another column with the corresponding 4-digit year.

You need to make sure that if the date is formatted as "08" then you need to put an apostrophe before the 08 ('08) for the index/match formula to read it. You have to do the same for the 2-digit year.

In the main Smartsheet, you need to add 4 extra columns--Month, Day, Year, and the Helper column (this column will be the one you map to the pdf).

The formulas to split out the date (mm/dd/yy) and produce what you want are as follows. Then it is just an easy formula to combine the 3 split out columns to create the actual date text.

Month: =INDEX({Month}, MATCH(LEFT([Date of Letter]@row, 2), {Month Number}, 0))

Day: =MID([Date of Letter]@row, 4, 2)

Year: =INDEX({Year}, MATCH(RIGHT([Date of Letter]@row, 2), {Year Number}, 0))

Date Helper: =Month@row + " " + Day@row + ", " + Year@row

See attachments.

SteverinoD

Would be great to get a fix for this, currently using IF statements to change month numbers to string values for a doc, thanks @Rebecca Ortinez , used helper columns to parse the data:

Temp Date format: 30/08/23 (DD/MM/YY)

4 columns:

Day: =LEFT([Temporary Form Expiry Date]@row, 2)

Month: =IF(MID([Temporary Form Expiry Date]@row, 4, 2) = "01", "Jan", IF(MID([Temporary Form Expiry Date]@row, 4, 2) = "02", "Feb", IF(MID([Temporary Form Expiry Date]@row, 4, 2) = "03", "Mar", IF(MID([Temporary Form Expiry Date]@row, 4, 2) = "04", "Apr", IF(MID([Temporary Form Expiry Date]@row, 4, 2) = "05", "May", IF(MID([Temporary Form Expiry Date]@row, 4, 2) = "06", "Jun", IF(MID([Temporary Form Expiry Date]@row, 4, 2) = "07", "Jul", IF(MID([Temporary Form Expiry Date]@row, 4, 2) = "08", "Aug", IF(MID([Temporary Form Expiry Date]@row, 4, 2) = "09", "Sep", IF(MID([Temporary Form Expiry Date]@row, 4, 2) = "10", "Oct", IF(MID([Temporary Form Expiry Date]@row, 4, 2) = "11", "Nov", IF(MID([Temporary Form Expiry Date]@row, 4, 2) = "12", "Dec", ""))))))))))))

Year: =RIGHT([Temporary Form Expiry Date]@row, 2)

Date Helper Column: =Day@row + "-" + Month@row + "-" + "20" + Year@row'

Formatted to: DD/MMM/YYYY

Probably easier ways to do it but still a real shortcoming of the software