Sheets Tip 328: Happy Holidays! + 2025 Calendar Formula



Hi Reader,

Welcome to the Google Sheets Tips newsletter #328, your Monday morning espresso, in spreadsheet form!

This is the last Google Sheets Tips newsletter for 2024. A big thank you for being part of this journey and reading these tips.

Your Monday-morning-espressos-in-spreadsheet-form will return on the 6th of January 2025.

In the meantime, I wish you all a wonderful holiday season with your loved ones.

Tip 328—creating a 2025 calendar with a formula—is found below, but first:

➜ 2024 In Numbers

  • 44 Monday morning espressos in spreadsheet form a.k.a. these Google Sheets Tips newsletters (full archive here)
  • ~55,000 of you get the email each week and about 23,000 of you actually read it (well done you!)
  • ~300 real espressos consumed ☕
  • 14 blog posts on the website
  • 2 million website views
  • Launched Sheets Insiders in September...now up to 413 members! 🙏
  • 1 new country (Slovenia hike - spectacular!)
  • 1 new lake (Smith Mountain Lake - also amazing)

It's been a fun, challenging, and interesting year!

Thanks for being part of it 🙏

I'll share more data and takeaways in an end-of-year review post that I'll publish... well, at the end of the year.

➜ Elsewhere

  • Sheets Wrapped is a fun project from my friend and fellow Google Sheets developer, Andrew Kamphey. It's like Spotify Wrapped but for Google Sheets.
  • A wonderful use of calendar heatmaps to show the play rate of Mariah Carey's classic "All I want for Christmas is you" song. Fans of the song will be disappointed to hear that listening rates have slipped slightly from a 2022 peak.

➜ Sheets Tip #328: 2025 Calendar Formula

We're going to create a calendar in Google Sheets with a formula!

In row 2, starting in column B, type in the weekdays as headers, i.e. "Monday", "Tuesday", "Wednesday", etc.

Then, in cell B3, add this SEQUENCE formula:

=SEQUENCE(53,7,DATE(2024,12,30),1)

And voila! It's as simple as that.

Now, let's make it prettier.

Firstly, let's change the date format to something more readable.

Highlight the whole table and go to the menu: Format > Number > Custom date and time

Then select the format you want. I chose this day and month combination:

Next, we'll hide the 2024 dates and the 2026 dates with two conditional formatting formulas.

Again, with the data highlighted, go to the menu: Format > Conditional formatting

Select "Custom formula is" under the Format rules and enter this formula:

=B3<DATE(2025,1,1)

Set the text color and cell fill color to white:

Add another rule with this custom formula to remove the 2026 dates from the end of the calendar:

=B3>DATE(2025,12,31)

Again, set the text and fill colors to white.

Now, add another rule with a custom formula to highlight the first day of the month:

=DAY(B3)=1

And set the fill color to yellow.

Add the final conditional formatting rule with this custom formula, to differentiate the months:

=ISEVEN(MONTH(B3))

Set the fill color to grey or light blue.

The order of the rules is important. It tells our Sheet the order of precedence, i.e. which rule to apply first. Make sure the rules are in this order:

Add a nice cell fill color to the header row (manually!) and we're just about done. I'll leave you to add any finishing touches!


Wishing you a great holiday season and see you in 2025!

Cheers,
Ben

P.S. "Oh, sheet!" might be an appropriate response

Google Sheets Tips

Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.

Read more from Google Sheets Tips

Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #354, your Monday morning espresso, in spreadsheet form! In last week's newsletter we compared wide and tall data, and what the pros and cons of each data "shape" were. Today, I want to show you how to move from one format to the other. How to transform tall data into wide data and vice versa. Going in one direction is easy. Going back, in the other direction, is less so. Find out which way round below! ➜ News I. It's...

Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #353, your Monday morning espresso, in spreadsheet form! Today we're looking at the shape of data. It's foundational concept that underpins pivot tables and charts in Google Sheets. Once you understand the difference between wide data (for charts) and tall data (for pivot tables), you'll be off to the races! This week is all about the pros and cons of each data shape. Next week, I'll show you how to use formulas and...

video preview

Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #352, your Monday morning espresso, in spreadsheet form! If you're a parent, then you've undoubtedly watched a lifetime's worth of Disney movies. You can appreciate the genius storytelling, rich characters and beautiful visuals. (But I'm sure you'd also be content to watch a film once, not 10 times!) Anyway, there's a scene in the Disney film Ratatouille where Remy bites into a strawberry and a piece of cheese at the...