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).