Putting Reminder Events in gCal for HH:MM unit goals (OVERKILL)

This is from my new “Overkill” series of posts. :wink:
(In other words, I’d consider this an advanced, why-would-you-do-this-to-yourself maneuver. It’s probably not newbee friendly.)

I’ve had a problem with a number of goals over the years that are meant to get me doing something earlier or later in the day. (Start Work Earlier, Lights Out Earlier, Break Fast Later, etc.) Since the time by which I have to do it changing as I go, the traditional reminder time doesn’t help me, so I forget at what time I’m supposed to do it, and blow right past it. The only way I’d found to remember it was to set an alarm and change it every day to match tomorrow’s goal time. #friction

Well, I’ve duct taped Beeminder, Google Sheets, and IFTTT together to sort of solve this, I think.

(For this particular use case, I have a stable rate for my goal, so I can “hard code” that in (if you can call it that), and I have “max safe days” set to 0, so that I have to do better every day than the day before. It’s also what makes tomorrow’s time predictable, I’m afraid, so if you’re not either skating the edge of the road every day, or wanting every day to improve over the day before by the rate you set, this won’t really help. Sorry!)

What I did:

I have an IFTTT recipe that triggers when there’s a new datapoint for a specific goal (‘start work earlier’, in this case) and that adds a new row to a Google spreadsheet (date, datapoint, comment–though only the datapoint is really needed).

I have an array formula that turns the decimal in the datapoint column into a HH:MM formatted string in another column. (I don’t actually use that column, though, so it could just be omited.)

The important column is the next one, which adds the road’s rate to the datapoint before turning it into an HH:MM string. (In this case it’s a negative rate, so it subtracts 5 minutes, in my case, from the datapoint first.) This gives me the start time for the next day.

Then, I created an IFTTT recipe that triggers when the Google sheet has a new row launching an action to create a Google calendar event with the start time of “tomorrow at {{column F}}”. You can fill out the rest however you’d like. If you just want it as a reminder, you can set the end time to any time you know will be past the start time (e.g. “Tomorrow at 23:59”) and be done with it, maybe deleting it after you’ve dispatched it, so it’s not in your way as an event for the rest of the day.

(Personally, I wanted to make it show me the length of my workday given the time I started, so I created another array formula for the end time, but that’s not necessary and I won’t say more about it unless someone wants to try this and needs help with it.)

Now, every time I enter my datapoint for the day, it creates an event in my calendar for the next day for the time that will be required by the rate (and max safe days moving the road to meet me, if I’ve gotten ahead of myself). No more forgetting that I have to start whatever goal at such and such a time!

Anyway, here’s the array formula I used for the start time, in case you want to try this yourself. (Put this in cell F of the first row of your spreadsheet. Also, it assumes that the datapoints will be in column B and that the road’s rate (in the example case -0.08333333333, or -5 minutes, per day) will be in cell B1.)

=arrayformula( if( row(F:F)=1, “Tomorrow’s Start Time”,(if((floor((B:B+B1-(floor(B:B+B1)))*60))<10,((floor(B:B+B1))&":0"&(floor((B:B+B1-(floor(B:B+B1)))*60))),((floor(B:B+B1))&":"&(floor((B:B+B1-(floor(B:B+B1)))*60))))) ))

My sheet looks something like this, though you don’t need the “length” info or the last column if you’re not using it to calculate an end time.

Let me know if anyone else tries this (and whether you come across any bugginess in the array formula).

1 Like

(Oh, and this could also be used to add a task to your IFTTT-connected to-do or reminders app, too, if you prefer that over adding it to your calendar.)

1 Like