Wrong display of date when mailmerging from Google Spreadsheet

  • Profile Image
    chinathinksbig
    Asked 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.

  • Profile Image
    Mike
    Answered 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
    chinathinksbig
    Answered 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
    Kyle 
    Answered 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
    Kevin_G
    Answered 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.