How to display a date for one day in a week that automatically changes weekly on Google Sheets

Google Sheets – TEXT, TODAY, WEEKDAY

If you are a busy admin nerd like me and have created a Google Doc or Sheet on the fly to meet your company’s demands for something or another, you probably also have a few Sheets lying around that are not 100% right. That obsessive-compulsive nature in you is niggling the back of your mind saying, “You can do this better!”

But the day-to-day race to get things done takes over and you move on to more pressing matter.

Until the next time you have to look at that Google Sheet and it starts bugging you again. Well, until you have a moment to fix it.

The Problem

For me each week I had a sign-up sheet for a makeup test for students. The coordinators who would add students to the sheet required that in the header rows, the date of the next Makeup Test be added for each week.

The makeup test was always at the same time each week: Monday at 15:30. I would then need to prefix this with the day and month. So it would look something like this:

Monday 28 Jan at 15:30

My original approach then was to open the sheet and change the date manually each week. Okay, I admit that on not just one occasion  I forgot to change the date much to the glee of the most persnickety of the coordinators who could happily call me out on my failure. Grrr.

I knew I could make this more efficient. I knew I could probably automate this process. Fortunately, the day finally came where  I found myself with 15 minute s of free time and this issue in my mind.

This post is the solution to the problem.

The Solution

Continue reading “How to display a date for one day in a week that automatically changes weekly on Google Sheets”