1

When I submit a Google Form, and I fill in a time the number that is entered into the spreadsheet is not the same number the spreadsheet will use for that time.

ie. If I enter 3:30 PM in a form and submit it. It enters the number 0.1458333333357590 into the google spreadsheet. If I type out 3:30 PM in the spreadsheet it is represented by: 0.1458333333333330.

Here is an example Form and Spreadsheet you can see this on. Go to the form, enter a time in the time picker, and enter your time as a string. You can then go to the spreadsheet and see that the submitted time does not equal the actual time the spreadsheet uses.

Why does this happen, and how can I fix it? This seems like a docs bug to me...

Related To: FILTER() Is not returning consistent results

New Question with entirely different premises.

  • 1
    I don't understand your examples. The spreadsheet shows 3:30 AM and 5:45 AM, different times. Neither of those is 3:30 PM. I just submitted 3rd entry, 3:30PM. It is represented by 0.645833333333333 as it should be (15.5 / 24). – user79865 Sep 29 '15 at 1:10
  • You answered this question in another question Normal Human! However, on the spreadsheet, when you submit a time and the string of your time I take your submitted time and display the float that it is comprised of, I then take your string and turn that into a time with TIMEVALUE() and display the float it is comprised of. So that I can highlight the inconsistencies. Submitted Time corelates to Submitted Time As Number and Submitted Text time corelated to Submitted Text Time As Number – Douglas Gaskell Sep 29 '15 at 19:56
  • My question was mostly about the inconsistency between writing "3:30 PM" in this post and quoting numbers such as 0.1458333... which correspond to 3:30 AM. – user79865 Sep 29 '15 at 21:21
3

There is a fractional time difference (milliseconds) between the time entered when you enter into the form vs directly into the sheet. Possibly this is a bug.

I think you can fix it by rounding the values: check for equality with a formula like

round(time1, 6) = round (time2,6)

-2

You simply need to change the formatting for the column you want the time displayed in. You are seeing the TIMEVALUE() value for the specified time.

Go to Format -> Number -> Time to see it displayed as you desire.

Or go to Format -> Number -> More formats -> More date and time formats if you'd like to customize it further.

  • 2
    Thanks for the reply James! That's not the problem sadly. I can format it till I'm blue in the face, but I CANNOT compare the times because they are not the same. 3:30 PM != 3:30 PM when their floats are not exactly the same. This prevents me from filtering any of my data based on time (or using DGET(), or QUERY()) – Douglas Gaskell Sep 28 '15 at 23:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.