Brought to you by:

Hi Reader,

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

Today, I'm sharing some formulas that I use all the time: date formulas. I do a lot of project management planning in Sheets so I'm always needing to generate sequences of dates. Below, you'll find a roundup of the most useful.

But first, I'm delighted to share today's sponsor with you.

If you're serious about working with data then learning how to code with the R language is a great skill to add to your toolbelt. And there's no one better to teach you than my friend David, who runs R in 3 Months. He's an independent, dedicated technology educator (like myself) and he's kicking off his new course shortly. Please check it out:

Brought to you by: R in 3 Months The next level of data automation If learning Google Sheets skills has transformed your ability to work with data, you should know that there's another free tool that's just as much of a game changer: R. R is a powerful tool that allows you to automate data cleaning, create stunning visualizations, and generate reports. It is a coding language, but if you can handle Sheets formulas, you can absolutely learn R. And now’s the perfect time to start. R in 3 Months is a cohort-based program designed to help complete beginners master R with confidence. The next cohort starts soon and you can get 15% off with code BCRIN3. Learn More

➜ Sheets Tip #335: Useful Date Formulas in Sheets

In today's tip I want to share a bunch of useful date formulas.

These come in handy for project management work, for scheduling, for modeling, and many other time-based analysis projects.

Without further ado, here are six useful date formulas in Sheets:

Today

This outputs today's date.

Start of the Current Month

We can create a specific date, such as the start of the month, with a static DATE function as follows:

The syntax is DATE(year, month, day).

However, we can make a dynamic version that automatically updates using the EOMONTH function:

This outputs the first day of the current month.

EOMONTH gives the end of the month. The "-1" argument gives the previous month, i.e. the last day of the prior month. Then we add "+1" to get the 1st of the current month.

End of the Current Month

Start of the Next Month

End of the Prior Month

Next 12 Months

We use the SEQUENCE function to generate 12 rows of data that we turn into months:

Interestingly, we can also use the native array functionality of the SORT function to shorten this to:

It's also of course possible to create a simpler hard-coded version of this monthly sequence:

In our Sheet, these formulas look like this:

In my own work, I've used countless variations of formulas like this last one.

For example, I have a Sheet to track these newsletters and plan upcoming issues:

The date column formula is:

Stuck? Use AI to help

If you're struggling to get the formula to work, create a list of the first 5 or 10 rows of dates, then plug it into your favorite AI tool and ask it to create a formula for you.

E.g. try a prompt like this:

What's the Google Sheets formula that would generate weekly dates like this: 17-Feb-2025, 24-Feb-2025, 3-Mar-2025, 10-Mar-2025, 17-Mar-2025, 24-Mar-2025, etc.

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. An accurate summary of the current state of AI​