Hi Reader,

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

A big thank you to this week's sponsors who help keep this newsletter free to read:

➜ News

I.
​Into Apps Script? Don't miss the Totally Unscripted show this Wednesday, 20th March at 3pm EST.

This week's guest is Phil Bainbridge, from the University of York, who will share his insights on automating tasks with Apps Script.

Show link: Totally Unscripted: Level Up Your Productivity

_______

➜ Google Sheets Tip #295: Year-in-progress date formulas

On Twitter, I follow an account called Year Progress. It dubs itself as the "only progress bar you'd rather see go slower", which sounds about right.

This morning, it informs me we're already 21% through the year!

21%! Where does the time go?

Having recovered from this blow, I thought I'd better share it with you and see how we can create our version in Sheets.

Grab the nearest blank Google Sheet and follow along!

Today's Date Formula

Enter this formula in cell C2 to display today's date:

=TODAY()

To calculate how far through the year we are, we need to know the first and last days of the year too.

The simplest way is to type the dates "1/1/2024" and "12/31/2024" into cells of our Sheet.

However, these won't update automatically when the year changes. Let's make them dynamic formulas.

Dynamic Date Functions

The DATE function takes year, month, and day number inputs and outputs a valid date.

The YEAR function extracts the year number from a date.

So YEAR(TODAY()) will extract the year value (2024) from today's date.

Combine these, and we can get the first and last days of the year from today's date.

This formula, in cell C3, generates the first day of the year dynamically:

=DATE(YEAR(TODAY()),1,1)

And this, in cell C4, the last day of the year:

=DATE(YEAR(TODAY()),12,31)

These will roll over to 2025 automatically.

To calculate the number of days between dates in Sheets, we subtract them.

Thus, days gone is:

Today's date - First Day + 1 (to account for today)

In my case, I have today's date formula in cell C2 and the first-day formula in C3. So, in cell C6:

=C2-C3+1

and then the days remaining in the year are:

Last Day - Today's date

In cell C7:

=C4-C2

We're here:

So far, so good?

Year-In-Progress Chart

Although these numbers tell us how far we are through the year, they don't convey a sense of progress.

For that, let's call in the cavalry.

Enter the dazzling SPARKLINE function.

The SPARKLINE function is a magical function that turns numbers into mini-charts inside a single cell.

It takes two arguments: the data and chart options.

In our case, we'll specify a bar chart.

With the days gone and days to go data in cells C6 and C7 respectively, our formula is:

=SPARKLINE( C6:C7, { "charttype" , "bar" })

(Note the two "t"s in charttype)

The output of all this is our year-in-progress chart:

which looks like this in the wider context:

We have 79% of the year remaining.

Let's get after it team!

Advanced Extension

For those of you hungry for more...

I'm obsessed with the LET function and use it to simplify all my complex formulas.

In this example, we could capture all these intermediary steps as variables in the LET function.

We can create the chart with a single formula now:

Feel free to take it even further and customize the colors!

_______

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

Have a great week!

Cheers,
Ben

P.S. I feel your pain...​