Form Submission - Date Help - URL string

ConnorForm

Hello all, another form submission question. Have 2 sheets, and those sheets each have a form associated with them. On Sheet 1 Form 1 someone will place all campaign information in this form, one of them being a campaign start date. After form 1 is submitted, a link to form 2 is presented in sheet 1 that takes campaign info filled out in sheet 1 and prepopulates those campaign details in form 2 from form 1 but adds on creative details. One of the fields in form 2 'Creative Start Date' is prepopulated by the 'Campaign Start' field from form 1.

The problem I am facing is sections from form 1 are spit out as 10/03/24 in sheet 1, but when the creative form link is selected, the second form is prepopulating as 10/03/24 when the form submission requires a 10/03/2024 format for the team to submit.

Sheet 1 - Form 1 populates items in this sheet - ' Creative Taxonomy Form' is the link to form 2 - Campaign Start is field filled out in form 1 but prepopulates as 'Creative Start Date' in form 2

Clicked the link on row 3 and ideally would want the format of this date to read 10/03/2024 NOT 10/03/24 as the team has to fix it every time before they submit the form

Here is the part of the formula I feel like I need to change to get that full year to populate 2024 instead of 24:

+&Creative%20Start=" + SUBSTITUTE(SUBSTITUTE([Campaign Start]@row, "@", "%40"), " ", "%20"))

here is the full formula that gets Form 2 populating in sheet 1 currently has this formula tied to it with all of these fields referenced being prepopulated below:

=IF(OR(Status@row = "Account - Needs Creative", Status@row = "Account - Revise"), "https://app.smartsheet.com/b/form/f4472790ae074dc5bba64d36a513f463?" + "&Fiscal%20Year=" + [Fiscal Year]@row + "&Client=" + SUBSTITUTE(SUBSTITUTE(Client@row, " ", "%20"), "&", "%26") + "&Campaign%20Start=" + [Campaign Start]@row + "&Industry=" + SUBSTITUTE(SUBSTITUTE(Industry@row, " ", "%20"), "&", "%26") + "&Agency=" + Agency@row + "&Brand=" + SUBSTITUTE(Brand@row, " ", "%20") + "&Source=" + SUBSTITUTE([Platform / Vendor]@row, " ", "%20") + "&Medium=" + SUBSTITUTE([Medium_Conv]@row, " ", "%20") + "&Campaign%20End=" + [Campaign End]@row + "&Channel=" + SUBSTITUTE(Channel@row, " ", "%20") + "&Campaign%20UID=" + [Campaign UID]@row + "&Target%20Market=" + SUBSTITUTE(SUBSTITUTE([Target_Market_Conv]@row, " ", "%20"), "|", "%7C") + "&Data%20Source=" + SUBSTITUTE(SUBSTITUTE([Data_Source_Conv]@row, " ", "%20"), "|", "%7C") + "&Targeting%20Type=" + SUBSTITUTE(SUBSTITUTE([Targeting_Type_Conv]@row, " ", "%20"), "|", "%7C") + "&Campaign%20Name=" + SUBSTITUTE(SUBSTITUTE([Campaign Name]@row, " ", "%20"), "_", "%5F") + "&Media%20KPI=" + SUBSTITUTE(SUBSTITUTE([Media KPI]@row, " ", "%20"), "-", "%2D") + "&Objective=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Business Objective]@row, "(", "%28"), ")", "%29"), " ", "%20") + "&Retailer=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Retailer@row, " ", "%20"), "&", "%26"), ",", "%2C"), ".", "%2E"), "-", "%2D") + "&Platform%20/%20Vendor=" + SUBSTITUTE(SUBSTITUTE([Platform / Vendor]@row, " ", "%20"), "#", "%23") + "&UID=" + UID@row + "&Account%20Person=" + SUBSTITUTE(SUBSTITUTE([Account Person]@row, "@", "%40"), " ", "%20") + "&Omni%20Planner=" + SUBSTITUTE(SUBSTITUTE([Omni Planner]@row, "@", "%40"), " ", "%20") + "&Creative%20Start=" + SUBSTITUTE(SUBSTITUTE([Campaign Start]@row, "@", "%40"), " ", "%20"))

In both form submissions the date will read 10/03/2024 but will populate in both smartsheets as 10/03/24 not sure if that is why it shows as such while filling out in form 2

SueinSpain

so not sure I completely follow this as have never prepopulated a form but dates…. oh my gosh always a pain.

The date field usually defaults to your computer's regional settings and confuses us all as I might be correctly set to UK (DDMMYYYY) but a colleague might still be incorrectly set as USA (MMDDYYYY)

If you highlight your date column and then in to top row far left click on the 3 horizonal dots, then more, then dates it gives you a choice of formatting. To ensure it is what you want choose something other than the regional option.

As I work with both UK and USA dates (so confused most of the time) I have opted to use DD-MMM-YYYY so today is 16-OCT-2024 to avoid any confusion.

Don't know if this helps but feel your pain

Georgie

Hi @ConnorForm,

Have you tried formatting the date columns in your sheets? When I tested and formatted my date column to the “26-Apr-2018” option and then opened the form attached to my sheet, the date field on the form populated as DD-MM-YYYY. To clarify, I’m based in the UK and have my regional preferences set to UK - when I changed my regional preferences to US, the date populated in the form as MM-DD-YYYY. 

See this article for more information on formatting date columns: Apply standardized date formats in your sheet. 

Does that help?

Georgie

Paul Newcome

It looks like you may have some advice to look into for the actual date formatting, but I also wanted to give some advice on your overall formula. I use formulas to generate the pre-filled form URLs quite often, and one thing I have found to be VERY useful is wrapping the entire thing in one SUBSTITUTE to cover all instances of the same character. For example, spaces need to be swapped out with "%20". Instead of manually typing that into your field names and repeating that over and over again for every field, you can take care of all of them in one fell swoop with something along the lines of

="form url?" + SUBSTITUTE("Fiscal Year=" + [Fiscal Year]@row + "&Client=" + Client@row + "&Another Field Name=" + [Another Column Name]@row, " ", "%20")

Certain characters will need to have the SUBSTITUTE wrapped around the field name or manually entered if they are used elsewhere in the URL. An example of this would be a field that could have a % in it. You don't want to replace all "%" because then your "%20" would change. Same thing with the "&". You don't want to replace every single one of them in the string, but for the majority, you can use the above.