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​