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