I'm using Collect v1.1.5 and aggregate v0.9.4 installed on a google
appspot.
I've created forms using Build (v0.85).
Some of the questions in my form are date data-type questions.
I love that aggregate let's you export into google spreadsheets... but
its really unfortunate (frustrating) that the date format from date
data-type questions is not recognizable by google spreadsheets, in
that when I try to generate a graph it does not accept the date
questions results ... it will accept the automatically generated
submittal date but not questions that asked the enumerator to chose a
date... it seems that ODK uses different date formats for the
automatically generated "form submission date" vs. dates from
questions, i.e.:
Sunday, February 27, 2011 1:20:19 AM UTC --> example of submission
date
Sun Feb 27 00:00:00 UTC 2011 --> date format from a response to a
question using a form generated by Build
I can write a series of formulas that parse & reconstruct the "right"
format in my google spreadsheet - but this is just silly. (And also
significantly reduces how "automated" I could have made my graphics
update from aggregate exports.)
I think if the day of the week & month were not abbreviated by ODK
(i.e., "Sun" & "Feb"), then google spreadsheet would accept them for
graph generation... just a guess.
Matt
Matt,
Can you file an issue on our code website? This is something we will
need to track as it might involve multiple people. Also please attach
the form that caused the problem to the issue.
http://code.google.com/p/opendatakit/issues/list - We are slow to
respond feel free to prod us but we do try to work off the issues on
the list.
Thanks,
Waylon
···
On Mon, Feb 28, 2011 at 6:45 PM, Matt Basinger wrote:
> I'm using Collect v1.1.5 and aggregate v0.9.4 installed on a google
> appspot.
>
> I've created forms using Build (v0.85).
>
> Some of the questions in my form are date data-type questions.
>
> I love that aggregate let's you export into google spreadsheets... but
> its really unfortunate (frustrating) that the date format from date
> data-type questions is not recognizable by google spreadsheets, in
> that when I try to generate a graph it does not accept the date
> questions results ... it will accept the automatically generated
> submittal date but not questions that asked the enumerator to chose a
> date... it seems that ODK uses different date formats for the
> automatically generated "form submission date" vs. dates from
> questions, i.e.:
>
> Sunday, February 27, 2011 1:20:19 AM UTC --> example of submission
> date
>
> Sun Feb 27 00:00:00 UTC 2011 --> date format from a response to a
> question using a form generated by Build
>
> I can write a series of formulas that parse & reconstruct the "right"
> format in my google spreadsheet - but this is just silly. (And also
> significantly reduces how "automated" I could have made my graphics
> update from aggregate exports.)
>
> I think if the day of the week & month were not abbreviated by ODK
> (i.e., "Sun" & "Feb"), then google spreadsheet would accept them for
> graph generation... just a guess.
>
> Matt
>
> --
> Post: opendatakit@googlegroups.com
> Unsubscribe: opendatakit+unsubscribe@googlegroups.com
> Options: http://groups.google.com/group/opendatakit?hl=en
>
Hi Waylon,
I've got the same issue 
I'm using a form which records (amongst other things) the Submission
Date, Start and End of data capture. An extract from the CSV file
downloaded from ODK Aggregate is shown below.
"SubmissionDate","Start","End","Deviceid"
"Monday, March 28, 2011 1:03:30 PM UTC","Mon Mar 28 12:12:38 UTC
2011","Mon Mar 28 12:14:05 UTC 2011","359028039382641"
"Monday, March 28, 2011 1:03:45 PM UTC","Mon Mar 28 12:19:49 UTC
2011","Mon Mar 28 12:21:02 UTC 2011","359028039382641"
I've in fact got two problems ...
Problem 1: The dates use different formats
SubmissionDate = "Monday, March 28, 2011 1:03:30 PM UTC"
Start date/time = "Mon Mar 28 12:12:38 UTC 2011"
Problem 2: The second problem is that when these dates are imported
into Google Fusion (even although they appear to be recognised as date/
times - under edit/modify columns) - when you try to sort them only
Ascii sort is supported. This means that Thursday comes before
Wednesday - regardless of the actual numeric date!)
e.g. yyyy-mm-dd hh:mm:ss (2011-04-07 15:43:00) which then sorts
correctly as a date or in ASCII.
Kirby
···
From my perspective it would be best to have the output in ISO format
Hello,
Any solution for this issue? (@yanokwa ) I’m using XLSFrom/Excel to build my forms and want all date fields to only display dd/mm/yyyy and not something like “Sun Feb 27 00:00:00 UTC 2011”.
Posting an example of an Excel formula that I've used in similar situations as a workaround since the string should always be the same length. Apologies that it's not helpful for you @Matt_Basinger but might assist someone else.
Once I had start and end times in the following format:
2015-11-26T10:34:29.741Z
I needed to calculate the elapsed time for the survey so I used the following on the cells to remove the T, Z, and fractions of a second:
=CONCATENATE(LEFT({{iso date-time cell}},10)," ",LEFT(RIGHT({{iso date-time cell}},13),8))
Then this to get the elapsed time:
=TEXT({{reformatted end time cell}}-{{reformatted start time cell}},"h:mm:ss")
Sometimes needing to also follow these simple instructions to refresh the cell formatting in Excel.
@Vanubhav, in order to get the text in your most recent post unto a format that can be parsed by Excel as a date I would use the following (with A1 being the cell containing Sun Feb 27 00:00:00 UTC 2011):
=CONCATENATE(RIGHT(LEFT(A1,10),6),", ",RIGHT(A1,4))
Thanks, but I was wondering if this could be implemented in the XlsxForm itself.
I'm not aware of a way to change the way the date is returned in the data. I think it has to do with the server aggregating/returning the data and not the XLSForm. Might be something for the Features category, or something to file an issue about as mentioned by Waylon.
1 Like