What is JotForm?
JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.

At JotForm, we want to make sure that you’re getting the online form builder help that you need. Our friendly customer support team is available 24/7.

We believe that if one user has a question, there could be more users who may have the same question. This is why many of our support forum threads are public and available to be searched and viewed. If you’d like help immediately, feel free to search for a similar question, or submit your question or concern.


  • Profile Image

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

    Asked by ianljohnstone 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? 

    Ian 

    Page URL:
    https://form.jotformeu.com/52172939197365

    Screenshot
    form calculation excel CSV length fill
  • Profile Image
    JotForm Support

    Answered by Boris 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.