Output of a Timer widget is showing as HH:MM:SS in spreadsheets

  • Profile Image
    Asked on October 12, 2015 at 04:24 AM


    I am using the form calculator widget to record the length of time for a user to fill in a form.  The results are then down loaded as a csv file to excel.  Each form takes about 2 minutes to complete.  


    However the results are being imported onto excel as an hh:mm:ss.  They need to be imported as mm:ss

    is there a work around for this? 


  • Profile Image
    Answered on October 12, 2015 at 11:19 AM

    I have cloned your form into my account and performed some testing on it, and I have noticed the following. When downloading submissions as Excel or CSV, there is actually no time format being applied to these columns for me in Libre Office:

    Similar happens in an integrated Google Spreadsheet - at the start, the Timer field output doesn't have a time-formatted column. Instead, the values are just the same as Timer is showing them, such as 2:05 as an example.

    After a while, Google internally adds a locale setting into the integrated Spreadsheet, which breaks subsequent submissions into displaying their Timer as HH:MM:SS.

    I believe that the same thing is happening for you in your version of Microsoft Excel - that Excel is automatically adding formatting to these values, when there should have been no format applied to them by default. As this is being done automatically for you by Excel, you can try manually switching formatting of the affected cells/column to Plaint text, instead of letting Excel format it as Time.

    One of the ways to do this is when you are importing/opening your CSV file in Excel for the first time, Excel will probably ask you how to format the spreadsheet. Select the Timer column, and set its formatting to Text - and Excel would display data as it is, rather than re-formatting it into a HH:MM:SS time.

    I hope this helps. Please let us know how it goes.