Dealing With Google Sheets Dates

Dates are weird.

Not that kind of date. Dates in programming. Dates in spreadsheets. It can be confusing dealing with dates and calendars because time is fickle.

I made a dynamic year calendar to better wrap my head around it, and I'll probably stick with proper calendar apps going forward after going through the trouble... 🤣

But, it was a good exercise, and here's what I discovered.

Video Walkthrough

Here's the whole walkthrough of me building this if you'd like to come along for the 14 min ride:

Setup

I wanted this to look slick. And to have multiple lines per day to write stuff, so I setup the rows and columns accordingly, removed gridlines, and added some nice styling.

I also knew I wanted everything to change dynamically. So all I would have to do is enter January 1st of whatever year it was in the top cell.

First Day of Month

To get the numbers to start on the correct first day of the month, I used the WEEKDAY() function.

The numbers 1-7 correspond to Sunday - Saturday. By testing whether January 1st of the year is the same WEEKDAY as the current day, I'm able to display 01 in the correct slot for the first day of January.

Then on each of the next days through Saturday, I can test for that as well as if the previous day was in the month. If it was, add 1 to that day, if it wasn't and the current day also isn't, leave it blank.

Rest of the Month

For the rest of the month, we simply add 1 to the previous date until the second to last line. Here we start testing for the last day of the month in similar fashion by using the TEXT() function.

TEXT() lets us turn the number into the month format by entering "mmmm" as the second argument. So it tells us that 26 is in January. We then test if that day plus one is equal to the month version of the first day of that month ($B1). If it is, we keep adding 1, if not, we return a blank string.

Clean Up the Start and End

Once we've got all our data entered, we can color in the cells that are not being used at the start and the end of each month. These are where we've got the blank string values before the first day of the month and after the last day of the month.

By applying conditional formatting to these ranges, we can color in everything where the top cell was blank.

Copy Months

After this, it's a matter of copying down the completed months to the next months. And in order to make each month pull from the previous, we use the EDATE() function. This allows us to grab January 1st in B1 and then return one month from then in this simple function: =EDATE(B1,1).

December Quirks

December is the only one with a quirk. When we start checking for the end of the month, we need to also check for the year to match instead of just the month. I ran into some funky values when I left out the year check.

We do this in the same way we did our previous TEXT() function only we wrap another TEXT() function checking for the year ("yyyy") within an AND() function.

Hope this is helpful for you! Making odd use case projects is a good exercise for sharpening skills on some of the oft underused Google Sheets functions.