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.

Improve this question
3
  • 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, 2015 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, 2015 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, 2015 at 21:21

2 Answers 2

Reset to default
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)

Improve this answer
-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.

Improve this answer
1
  • 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, 2015 at 23:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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