Suggestion for Cannot Find Method Formatdate(String,String,String)

Due to a change in how Sheets is processing the Date from Form Responses, values previously returned as Dates are now coming in as Strings. Users must coerce the value to a date in a new column to correct this.

  • Create a new column pointing to your current Date/Time values and coerce it to a date by adding 0. For a sheet where my Date/Time values are in column B, do this with the following:

The value in cell B2 returns as a Date rather than a String.
You can apply this using an array formula as well. The advantage of using an array formula is that the formula copies down the column for the entire sheet.

The formula would look similar to this: =ARRAYFORMULA(IF(ISBLANK(A2:A),"",IFERROR(B2:B+0))

Place this formula in the 2nd row of the new column. This formula is then referenced in Event-o-Matic as the new column for your Date or Time value. Use the formula in as many columns as needed, e.g., Start Date, End Date, Start Time, End Time, referencing each column appropriately.

You may need to change the formatting of the column to display Date/Time properly.

In the array formula, the A2:A references the first column in the sheet, typically the Timestamp, to prevent the formula from expanding through blank rows in column A.


Document Version Date Description of Change


Articles in this section