(Originally written as a Google Docs Forum post)

I have a google form that records data such as

2/2/2015 12:41:49	179.2	0.81	140
2/3/2015 14:34:24	177	1.06	147
2/4/2015 12:59:45	179.2	1.21	153
2/5/2015 11:15:26	177.2	1.14	148

I am interested in performing a lookup on the *date only*, with the intent of recasting the data in another tab within the same sheet.

The destination tab has a prepopulated date which can be used to perform a lookup. However, as it is the date only the underlying numeric date value is distinct from the underlying date value populating the datetime stamp field in the form data. That combined with the way that VLOOKUP performs fuzzy matching means that the data retrieved is generally associated with the previous date.

Here is an example showing that exact issue.

The formula used to perform the lookup is

=vlookup(A2,Sheet1!$A$2:$D$5,2)

What is needed is for the source datetime column to be evaluated for just the date part, or to evaluate for a matching substring. I don’t think hlookup and vlookup can do this.

One possible way to do it is to add a column in the form’s sheet that performs the =INT(datetime stamp) operation and then point vlookup at that column, but I am reluctant to prepopulate the spreadsheet with that data because in my experience adding data to a form-derived spreadsheet causes the form to populate the next blank row beneath the data stored, and thus defeats my purpose.

Writing this, I wonder if I can add a hidden field that performs this operation at submit, splitting datetime into date and time columns in addition to the datetime column.

Beyond that, I was interested in learning how to perform the date extraction algorithmically anyway. I have seen similar questions posed that reference array formulae and the use of QUERY. I had thought given my desire to recast the data in a given additional tab with various hardened formatting features an array formula is not what I want, but I was open to suggestions.

I resolved this on my own.

The solution was to add two columns in the form’s destination sheet, DATEINT and TIMETEXT. I have placed them as column B and C.

Then, I added the following array formulae to the first row beneath the new columntitles:

= ArrayFormula(INT( A2:A ))

= ArrayFormula(TEXT( A2:A , "H:MM:SS" ))

An unused approach was to convert the date to text as well:

= ArrayFormula(TEXT( A2:A , "M/D/YYYY" ))

The array formula autofills down the sheet on the populated rows and will extend as data is added to the field. It does not impede form data collection.

This answer on the forums was helpful.

Researching this solution, implementing it, and writing about it took about an hour and a half.