What is JotForm?
JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.

At JotForm, we want to make sure that you’re getting the online form builder help that you need. Our friendly customer support team is available 24/7.

We believe that if one user has a question, there could be more users who may have the same question. This is why many of our support forum threads are public and available to be searched and viewed. If you’d like help immediately, feel free to search for a similar question, or submit your question or concern.


  • Profile Image

    Wrong display of date when mailmerging from Google Spreadsheet

    Asked by chinathinksbig on November 14, 2015 at 03:53 AM

    Hi,

    I put a date field in my form http://www.jotform.com//?formID=53091386635965 that automatically generate a date that is 8 days after the submission date.

    It's displayed normally in mm/dd/yyyy format both in jotform submission pool and google sheet, but errors occur when I try to use google spreadsheet to mailmerge this date into an email. the value displayed on this field is a series of number instead, like 11/19/2015 becomes 42327. Same thing happens when I copy the cell in google sheet and paste value only. I don't know if that has anything to do with google sheet's date picker.

    Is there anyway to quickly have this solved? I'm going to do a huge load of mailmerge soon...

    Thanks for any support given.

    mailmerging date field
  • Profile Image
    JotForm Support

    Answered by Mike on November 14, 2015 at 03:40 PM

    Are you referring to the following date field?

    It's displayed normally in mm/dd/yyyy format both in jotform submission pool and google sheet, but errors occur when I try to use google spreadsheet to mailmerge this date into an email. 

    Could you please provide us with the steps to reproduce this issue? We will need to test this from our side in order to troubleshot it further.

    Thank you.

  • Profile Image

    Answered by chinathinksbig on November 15, 2015 at 03:45 AM

    one way is to ctrl+c copy this field in google sheet, then do special paste>> paste value, then the value pasted is a 5 digit nuber.

    another way is to mailmerge contents in this field using some google sheet add-ons, like the one I'm using, Yet Another Mailmerge. the content seen is email is not the date but the same 5 digit number.

    This 5 dig number seems to be the number of dates between 1900.1.1 and today.

     

    And strangely, this does not occur in the source sheet that is directly linked to jotform (https://docs.google.com/spreadsheets/d/1Ek2DP_tEt13xapQk1xjDZRvAAzRb-IE_lWGZqWbb2hI/edit#gid=112095282), but exist in field named "fee deadline" in the 1st and 3rd tabs of this sheet (https://docs.google.com/spreadsheets/d/1bXbPVgyaFtdDqSKJ616C4f7jBFXABgN04zUAuelJjFA/edit#gid=1912717561).

    The content in the problematic sheet is imported directly from the source sheet as you may can see the formula I use. And because I can't label and note on the source sheet, the problematic sheet is the one I'm going to use for mailmerge. Hope I explained my problem clearly.

    Thanks.

  • Profile Image

    Answered by Kyle  on November 15, 2015 at 05:08 AM
    When you paste "only the values", you strip everything from that value, including its original date format. Make sure that the column where you paste those stripped values are formatted for dates in mmddyyyy. I've done numerous mail merges (sotware & cloud-based) and you wont get the serial numbers when the date column is formatted appropriately.
  • Profile Image
    JotForm Support

    Answered by Kevin_G on November 15, 2015 at 12:38 PM

    Hi,

    What kyle says makes sense, however , I was able to copy and paste the date value in another spreadsheet without any problem : 

    Let us know if it works.