Google Sheets – How to Stop Google Sheets Formatting Forms Response as Date

formattinggoogle sheetsgoogle-forms

I have a Google Forms with a question that contains a drop-down box with the following values:

 - Fewer than 10
 - 11-25
 - 26-50
 - More than 50

If the user selects 11-25, Google Sheets renders the response data like this: ono halp

Under the formats dropdown in the toolbar, selecting "Plain Text" has the cell render as this date. Selecting "Normal" gives just 40,872, and selecting any other type of format gives $ 40,872.00, 980928:00:00, and other various numbers that aren't anything like 11-25, depending on what option I choose.

The workaround is to change the Forms response option to 11-25 apples, or to recognize when manipulating my data that '11/25/2011' really means '11-25 apples'.

But is there a way to get Google Sheets to stop turning 11-25 into a date?

Best Answer

You have to format the column in spreadsheet mode before any data is entered.

Here we have a form about waffles. The cells have been formatted before sending it to the recipients.

How many waffles would you like

And here is the result after a user fills out the result.

A lot of waffles

Related Solutions

Google Sheets – How to Filter Based on Dropdown List of Months

To convert YYYY, MM, DD to an actual Date object, you need the DATE command. The following works:

=filter(A6:F371,A6:A371>=DATE(2015,1,1),A6:A371<=DATE(2015,1,31))

But I see you want to filter based on dropdown entry. For this I would use MONTH function. For example, suppose cell J10 has validation rule: list of items

01 (January), 02 (February), 03 (March), and so on

Then your filter in J11 could be

=filter(A6:F371,MONTH(A6:A371)=VALUE(LEFT(J10,2)))

The idea here is that the names of month help the user in selection, but the filter uses the numeric format (month number).

Google Sheets – Forms Data Manipulation

Short answer

Add a new sheet.
Add the following formulas to the new sheet

Cell A2 -> Headers

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,0)

This could be repeated several times, one for each header column, just change the last parameter accordingly.

Cell B2 -> data

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,mod(row()-2,5)*2+1,1,2)

Fill down as necessary

Explanation

OFFSET() is used to get the values from the source data.
'Form responses 1'!$A$2 is the start point to count columns and rows.
row() is used to increment the row and column iterators.
ceiling((row()-1)/5,1), row iterator, is used to set the row from the source data.
mod(row()-2,5)*2+1, column iterator, is used to set the column from the source data.
In the data formula the last parameter, indicates the number of data columns.

In case that are several "header data" columns, add 1 for each of them. I.E. if there are five "header data" columns, and six "data columns" the column iterator should be

mod(row()-2,5)*6+5

The final data formula to be added to the F2 cell is:

=OFFSET('Form responses 1'!$A$2,ceiling((row()-1)/5,1)-1,mod(row()-2,5)*6+5,1,6)

In the row and column iterators, 5 is used because there are 5 columns for "record data".

Demo

Spreadsheet