Answered

How can I remove time section from response to a date question?

  • 8 August 2022
  • 7 replies
  • 304 views
J
Userlevel 1
Badge

I am asking DOB and when I download responses I get this format

2016-03-03T00:00:00.000Z

 

How can I get this data to be only the date without having to manually remove the time stamp? So it becomes this?

2016-03-03

 

Thanks

icon

Best answer by john.desborough 8 August 2022, 12:42

View original

7 replies

john.desborough
Userlevel 7
Badge +6

@Jane Hartnell - if you are downloading this into a spreadsheet you can ‘format’ the column to show just the date, based on the options available in the software you are using. 

 

des

If your third coffee of the day is not helping you with your Typeform, maybe we can help you find your answers...
J
Userlevel 1
Badge

Thanks Des

 

However I don't seem to be able to solve it this way. I download as excel and whatever date format I choose it remains as is?

 

When I look at the responses in typeform it shows just the date.

 

Also - I have some yes and no questions - when i look in typeform it shows the answer of yes or no, but when downloaded it shows a number - can I change this to show the yes or no?

john.desborough
Userlevel 7
Badge +6

@Jane Hartnell - on the date field: go to another column and use the =int(cell_reference)  function and that will turn  the date and time string into the date only.  ie if your downloaded time stamp is in column e, then say in column M you put the following: =int(e2)   to convert the value in cell e2.. copy the formula down the rest of column M

you can also do the same sort of conversion logic on the yes no downloads as 1/0 

  • create a column and use the =if(a2 =1, “yes”, “no”)  expression or something similar 
If your third coffee of the day is not helping you with your Typeform, maybe we can help you find your answers...
J
Userlevel 1
Badge

Hi Des,

 

Thanks so much for your help

 

I solved the Yes/No by downloading as csv and pasting into an excel template with formatted number cells and using IF

 

Can you assist with the exact formula for =int ? I have not used this before but I am keen to! This is what is currently happening:

 

My date is in cell e2 as this 2016-03-03T00:00:00.000Z

and in M2 I type exactly =int(e2) I get #VALUE

 

What am I doing wrong?

john.desborough
Userlevel 7
Badge +6

@Jane Hartnell - here’s an article on converting to date formats in excel. Best thing is try the various options therein. 

alternatively, Connect your typeform to Excel and have all the data pushed to the Excel file each time a new entry is added (when you create the integration, you will have the option to download any existing records - i know there is a 2-3k limit in Google but not sure of Excel) .. that should resolve the issue.

 

I am more of a Google guy these days than excel.. sorry i can’t me of more help

 

des 

 

If your third coffee of the day is not helping you with your Typeform, maybe we can help you find your answers...
J
Userlevel 1
Badge

Fantastic - thanks Des

Gabi Amaral
Rank
Userlevel 7
Badge +5

Hey @Jane Hartnell! Were you able to follow's Des explanation and using the =int(cell_reference)  function for the date? Let us know if it worked! 

Everyone you meet is fighting a battle you know nothing about. Be kind. Always. =)

Reply


Still need help?

Don't be shy and ask the community.

Ask your question here!
Community T&Cs