Dashboard: Recurring Events

Now that we're all stuck in our house together, I'm building a dashboard to show what's upcoming on our combined calendar.  I'm using Google Sheets because I can display it large on our TV and we can see what's coming up next.  I wanted a countdown that shows how many minutes to the next event and I wanted events to disappear 5 minutes after they start.

One thing I quickly discovered that if an event happened every day, I was having to update it each day.  I figured there must be a way to do this programmatically.  So, I want it to be today's date, until after the date has passed and then I want it to be the next day instead.

Recurring Daily Event at 8:30 am:

=datevalue(now())+if(hour(now())>8,1,0)+(8.5/24)

(a) Start with the value for today's date.

(b) If it's past 8 am, add 1, otherwise, add 0.

(c) Add 8.5 hours.

On a given date, before 9 am, it will be set to today's date at 8:30 am.  At 9 am, it will switch over to 8:30 on tomorrow's date.  (Since it's hidden at 8:35 am, this gap is fine.)

Use military time and fractional time.  

For 2:45 pm, you'd change 8 to 14 and 8.5 to 14.75.

For 5 pm, you'd change 8 to 17 and 8.5 to 17.

For 10:15 pm, you'd change 8 to 20 and 8.5 to 20.25.

Recurring Weekly Event at 2 pm every Monday:

=datevalue(now())+if(weekday(now())>2,9-weekday(now()),2-weekday(now()))+(14/24)

(a) Start with the value for today's date.

(b) If it's past Monday, set it to next Monday, otherwise set it to this Monday (including today).

(c) Add 14 hours (2 pm) - see above for examples.

For this example, on Tuesday at midnight, it will switch to next Monday at 2 pm.  Before Tuesday at midnight, it will be set to this week on Monday at 2 pm.  (Since it's hidden at 2:05 pm, this gap is fine. You can work out more complex logic -- if (weekday > 2) or (weekday = 2 and hour > 14) -- if you need it to disappear quicker.)

You need two numbers for this to work:

(1) The weekday.  For me, the week goes 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday. This may be different based on your settings or locale, if you put "=weekday(now())" in a cell in Google Sheets or Excel you should be able to tell if your settings are different.  You'll use this number in the purple and red sections.

(2) How many days to the next weekday (from Sunday).  This is weekday + 7.  8=Sunday, 9=Monday, 10=Tuesday, 11=Wednesday, 12=Thursday, 13=Friday, 14=Saturday.  You'll use this number in the orange section. (Note: This is not QA tested over the course of an actual week yet, I'll update this if any adjustments are needed).

Dashboard Project

Now that we're all stuck together under house arrest, I've had a few spare moments here and there to work on a project I've always wanted - a dashboard.  I can project it onto the big TV, or eventually, a dedicated monitor in the kitchen. I'm starting with the calendar and I have a few goals - I want events to be displayed in chronological order, I want events to disappear 5 minutes after the event starts and I want a countdown to the event start. We all have separate calendars, so for now it's a manual process to enter items, but I want them in the correct order, no matter how I enter them, and I want recurring events to update themselves without any intervention from me.  

In the past, I've done smaller scale things, like a dashboard for my wife so she could see where I was along my commute or something that told me what emails I'd received recently, but none were polished or long-term. I'm thinking about what else I could do, like weather or Alexa stuff with IFTTT.

I'll blog about any interesting things I figure out along the way.