Writing An Unnecessarily Complex Function in Notion

What Is Notion?

If you have not heard of Notion, it's an "all-in-one workspace" or as I liked to call it, a better EverNote. The application is essentially a fully-featured markdown editor, allowing editing of each page using standard markdown and Latex in "blocks". In addition, Notion has integrations such as free images from Unsplash, mockups in your Figma, files in your Google Drive, and even code from CodePen.

Okay, enough praise for this thing, what does this have to do with programming?

Formulas In Notion

The programming-related part of Notion is its ability to use a query language called "Formulas" to create new spreadsheet columns. If you have ever worked with SQL or Excel functions, this will be familiar to you. Formulas allow the Notion user to define a single output based on other fields in the database, using relations much like in relational algebra.

The Problem

So I'm currently using Notion to write and sort my blog posts, but I came across a cool spreadsheet that uses Notion as a subscription tracker. I thought "wow, I use subscriptions, I should list out the total costs side-by-side".

So I duplicate this onto my own Notion workspace (jargon for your account), but I realized that the "Renewal Date" field had to be manually entered in. I thought to myself: "if all subscriptions are recurring on the same date each month or year, why can't this be automatically calculated by the subscription start date?".

And so I wasted a few hours automating one thing that probably should take 2 seconds to find and click each month or year. The beauty of automation.

The Formula

You might be wondering why it took so long for me to figure it out. It's because the formula editor for Notion is not great, and because there was no easy way to build a date field from separate integers like "year + month + date".

So I ended up with my final formula looking like this:

In case you wanted better formatting and easier for you to copy and paste, I structured it like GraphQL nodes down here:

formatDate(
  dateAdd(
    dateAdd(
      dateAdd(
        now(), 
        subtract(
          date(prop("Start Date")), 
          date(now())
        ), 
        "days"
      ), 
      if(
        and(
          equal(
            prop("Billing"), 
            "Monthly"
          ), 
          smallerEq(
            date(prop("Start Date")), 
            date(now())
          )
        ), 
        1, 
        if(
          equal(
            prop("Billing"), 
            "Yearly"
          ), 
          subtract(
            month(prop("Start Date")),
            month(now())
          ),0
        )
      ), 
      "months"
    ), 
    if(
      and(
        equal(
          prop("Billing"), 
          "Yearly"
        ), 
        smaller(
          month(prop("Start Date")), 
          month(now())
        )
      ), 
      1, 
      0
    ), 
    "years"
  ),
  "MMMM D, YYYY"
)

How I Got To This Formula

Let's break this chunk of formula down.

There are only a few constants I could use without adding extra fields to the database: now() for the current time, prop(field) for a column in the database, and then the rest are self-descriptive functions.

Calculating The Day

First, I had to figure out the correct day of the month. Since I would always know the very first renewal date of each subscription, the simplest way is to just add the difference between the current day and the renewal day, which would look like this: current_day + (start_day - current_day)

After turning it into a Notion formula:

I guess date() is not self-explanatory because it actually returns the day of the month.

Calculating The Month

Next, I will need to figure out the correct month with only the output from the previous query. There are now 2 branching options: if its a monthly subscription and the current day is larger than the initial subscription day, then I would need to increase the month by 1, otherwise increase it by 0.

I'm going to refer to the previous formula section as $day

By the way, for those wondering why we use operators instead of add() or and() this is basically why.

Of course, this didn't exactly work because of the yearly subscriptions. Whereas the month increases in a monthly subscription, the month of the yearly subscription don't exactly change every year. We can solve this by adding a nested if-statement for the else clause (the third argument in the if() function), and we'll end up with the correct month for both types of subscriptions.

I highlighted the added part in purple

Calculating The Year

Finally, the year is quite simple to calculate, since adding the month in the previous step will take care of the year transition. Instead, this part is mostly adding the year when its a yearly subscription and formatting it without the time it normally comes with. Again, I will use $month to signify the result from the previous step.

Wrapping Up

So this wasn't a complete waste of time, now I only have to input the date at which my first payment occurs and viola, automated next due date. I guess one thing I've learned is that spacing and indent really helps and that math operators are too ingrained into my head compared to reading these named functions. I hope you enjoyed my struggle and make use of this function for your own spreadsheet! Let me know if you write notes on paper or if you have another program to take notes in the comments below.

Bonus

In case anyone wondered, I actually only turned it to light mode for the screenshots but normally I use it in dark mode.

Also, if you want to make cool code screenshots as I did above (please only use it in conjunction with the actual code snippets), it's on carbon.now.sh

Follow me on Twitter @justinhodev if you'd like to keep up with my daily coding!