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

Weekly Date 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

Weekly Date Formula Google Sheets

As I mentioned, my goal is to display the date for each following Monday.  However, if we are on the current Monday of the test, we want to keep the current date. I then want to join this date with the day of the week and the time of the test.

Here is the total formula:

="Monday "&TEXT(IF(WEEKDAY(TODAY(),3)=0,TODAY(),TODAY()+(7-WEEKDAY(TODAY(),3))),"D MMM")&" at 15:30"

Breakdown

Let’s look at how it all works.

today()

The TODAY() formula returns the current day’s date. This formula is dynamic, meaning that it will update each day to the current date.

I have a tutorial specifically on how to use TODAY() here:

Add the Current Date When Data is Added to a Cell(Dynamic) – Google Sheets

The cool thing about today is that you can add and subtract from the current day’s date to get the date in the future or past. For example, TODAY()+3 would give you the date three days from now. Whereas TODAY()-5 would give you the date five days ago.

weekday(date,type)

WEEKDAY() returns a number that represents a day of the week. WEEKDAY() takes one primary argument, a date. In our case, the date will be TODAY().

WEEKDAY() also has an optional argument that allows us to define what numbering system we want. Option 1 starts on Sunday with a value of 1 and runs until Saturday. Option 2 starts on Monday with a value of 1 and runs until Saturday. Finally, option 3 starts on Monday with a value of 0 and runs until Saturday.

For our example, I chose option 3. It seem the most efficient way to calculate the other parts of the formula because zero was Monday.

So here is how we got the value of the weekday for the current date:

=(WEEKDAY(TODAY(),3)

This will result in a number value representing the day of the week for the current day’s date.

To get the following weeks date for Monday we must add the current day’s date to 7 days from now minus the weekday number of today. Confusing, huh?

For example, if today was 1 Jan 2019, WEEKDAY(TODAY(),3) would give us the result for Tuesday which is one (Monday is zero, remember). Now we would subtract 7-1 to give us 6 and then add that to the current date, resulting in 7 Jan 2019, which is the following weeks Monday. 

  1. 01 JAN 2019 =TODAY()
  2. 1 =WEEKDAY(TODAY(),3)
  3. 6 =(7-WEEKDAY(TODAY(),3)
  4. 07 Jan 2019=TODAY()+(7-WEEKDAY(TODAY(),3))

This is cool and all, but we don’t want to see the following weeks date on the day of our makeup test. We just want the current day’s date.

if()

The IF statement allows us to return a value based on the result of a condition. In our example, our condition is that IF it is Monday, display the current date, otherwise, display next weeks Monday date.

The IF() statement first takes the condition or argument. Next it take the result if true finally it takes the optional result if false.

=IF(CONDITION, VALUE IF TRUE, VALUE IF FALSE)

So in our example for Monday it would be:

=IF(WEEKDAY(TODAY(),3)=0,TODAY(),TODAY()+(7-WEEKDAY(TODAY(),3)))

concatenate with “&”

In Google Sheets, like LibreOffice or Excel, you can join different pieces of text or results of formulas together by using the ampersand(&). In our final product we want to join the words “Monday “ to the date, and join that to the text, ” at 15:30″. We can do this with (&).

="Monday "&IF(WEEKDAY(TODAY(),3)=0,TODAY(),TODAY()+(7-WEEKDAY(TODAY(),3)))&" at 15:30"

If you have been playing at home, you would have realized that although this seems to have joined things up, it has not displayed the date for Monday as we wanted. Instead it has given us a string of numbers. For example:

Monday 43493 at 15:30

That’s not what we wanted at all. That string of numbers, like in our example (43493), is the numerical format for the date we created. To convert this to a human readable date we need to add TEXT().

TEXT()

The TEXT() formula converts numbers into a specified format. It can convert numbers into comma-separated values, dates, currency and percentages.

For our purpose we are going to focus on the date pattern.

The TEXT() formula takes two arguments, the date and the format you want the date modified into:

=TEXT(DATE,FORMAT)

For example:

=TEXT("01/01/2019","DD MMM")

Which would result in: 01 Jan.

There are multiple options for how we could display the date, such as:

  • Tue 01 January 2019 =TEXT("1/1/2019","DDD DD MMMM YYY")
  • 1-01-19 =TEXT("1/1/2019","D-MM-Y")

There are many more variant for writing dates that can be found on the Google TEXT resource page.

Back to our example…

Now that we can modify our date to how we would like and we can join all our other text, the final result of our formula is:

="Monday "&TEXT(IF(WEEKDAY(TODAY(),3)=0,TODAY(),TODAY()+(7-WEEKDAY(TODAY(),3))),"D MMM")&" at 15:30"

Weekly Date for Each Day of the Week

How do you change to a different day for each week? For any day that does not include Monday, we need to add an extra number to the sum total of the WEEKDAY value:

="Day Of Week "&TEXT(IF(WEEKDAY(TODAY(),3)=#,TODAY(),TODAY()+(7-WEEKDAY(TODAY(),3)+#)),"D MMM")&" at 15:30"

For example, for Tuesday our formula would look like this:

="Tuesday "&TEXT(IF(WEEKDAY(TODAY(),3)=1,TODAY(),TODAY()+(7-WEEKDAY(TODAY(),3)+1)),"D MMM")&" at 15:30"

Conclusion

You can now see how to create a date that automatically changes weekly. I know now when I look at my Makeup Test Sheet, I am much more satisfied because I have automated this part of my work now, but that’s just because I am an admin nerd.

If you want to take a look at the sample sheet, you can access it here:

Weekly Date

Want a solid step-by-step course to become a pro at Google Sheets? Udemy has some professional courses that will turn you into an admin ninja!

I’m a huge fan of Justin Mares, Mastering Google Sheets course.*

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

~Yagi

Leave a Reply