My mom just related getting a “Go Mariners!” from unknown rando while walking around Reykjavik in an M’s hat. I assume she’s referring to the white bucket hat I bought for her a couple years ago when I dragged them along to a game. She sounded happy about it.
Google Forms date and time lookups
(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:
DATEINT= ArrayFormula(INT( A2:A ))
TIMETEXT= ArrayFormula(TEXT( A2:A , "H:MM:SS" ))
An unused approach was to convert the date to text as well:
DATETEXT= 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.
Late dinner
It’s solstice. My dinner plan was delayed, so food will be up after sunset, like 10:30 or so, in the bleak just-summer.
This gig, doing print layout, is totally fun. I spent years doing this stuff, beginning literally before the advent of desktop publishing when the Mac first came out in 1984. I was somewhat unsure if I would be at sea or what but given my even-longer-term familiarity with Adobe’s tools, this gig is somewhat like falling off a log. I’m even working at home, on my own gear – a lesson learned from Steve M., who emphasized that smallholder skilled workers could, in fact, control the means of production. I’ve never lacked a computer at home since that conversation.
The upgrade-go-round fiasco seems to have mostly ended, one laptop to the bad. I suppose I will source a mobo on eBay and revive the thing as I did with the MPT I killed last year.
I suppose I should excavate stuff of FB from the past six months as I did last post flurry.
June already
More than two months ago, my buddy Tod asked me to do a couple weeks of work with him at his warehouse. A full eight weeks later, I am about to start another month’s work on an InDesign project for the same employer, which should be fun. I developed pre-release samples as a contractor for a launch release of InDesign, probably around 2002.
In prepping my production environment for this I decided it made sense to finally spring for a 1tb SSD to pop into my main axe, a late-2008 unibody MBP (the last model with the HD and battery access door, ordered refurb from Apple in April 2009).
Long story short, I ordered and installed not one but two 1TB SSDs and neither were compatible. The first one is actually definitively described as incompatible with the specific model Mac by the manufacturer. The second was confirmed as compatible by the manufacturer before I purchased it, but after a successful clean install and data migration, the machine crashed out and rebooted into the recovery partition. I performed a recovery and went to reboot – but the device was (and remains) unresponsive. Presumably it is the motherboard. I guess seven years is reasonable use, but I’m still a little cheesed off about it – I certainly was not planning on replacing or upgrading for another five years or so.
So I ended up using the Crucial SSD on my other machine, an old Mac Pro 4,1 tower, and it’s a huge improvement. I had actually pre-ordered an OWC Mercury SSD for the dead laptop and had to RMA it. Immediately after learning the ’08 machine was dead I sourced a third-party refurb MPB from ’12 on eBay for about $500 and that’s what I’m writing on now. It could clearly benefit from an SSD swap too, and I see that OWC has just introduced a raft of new SSD options including a 2TB SSD for about $500, so that’s not out of the question. The CPU on the ’12 MPB is definitely beefier than that on the tower, but the difference between HD and SSD makes the tower far faster in use.
Simultaneously, I had decided to update our set of five-year-old iPhone 4 units and the replacement 5 units arrived this week. So between this and that, it’s been two weeks of intensive hardware and software chaos, hopefully resolved now for another five or six years.
Not sure how I feel about iOS 9 – Notifications in particular are a hideous annoyance that is apparently impossible to turn off globally and re-enable; predictably most apps install with Notifications turned on, in order to maximize ad impression opportunities. Annoyingly, apps that get *updated* ALSO have their on-install Notifications prefs reset. Which truly blows.
Anyway, off to remaster an InDesign workflow. Looking forward to it.