Using 'Date' field to label by Month


#1

Hello

I’m new around here, but I’m trying to set up a base that automatically breakdowns revenue, profit, owner’s page, expenses, etc using the Profit First methodology by Mike Michalowicz.

I feel like I’ve done pretty well so far. Here’s a look at the most important table that I’m trying to create.

And now I want to automate it more. So, here’s a link to the whole base so you can see how it’s set up (so far):

I’ve set up a Zapier connection so that Stripe automatically updates payments in the first table, labelled “Client Payments.”

Then the next table (“Monthly Revenue Totals”) calculates the total revenue for each month, based on what Stripe has recorded in the first table.

The last table (“Profit First calculations”) takes the total revenue for each month & applies the Profit First formulas to it, so that I can easily see how much I’ve got to, say, take as my own wage from the business bank accounts.

But there’s still too much manual work involved for my liking. That’s where I’m stuck & would love your advice…

Stripe records the date of the transaction. But to get AirTable to calculate the total earned for the month, I have to manually tag the correct month in another column. I’m wondering if there’s a formula or something to do that automatically.

Something like: if the date is 18 September 2017, select “Sep” in the next column.

If there’s a better / smarter way to achieve that, I’m all ears :slight_smile:

P.S. If you want to know more about the Profit First methodology, it’s all outlined in this book:

https://profitfirstbook.com/


#2

Hi @Alyssa_Martin

You could use Zapier to do this (I assume you mean linking the Client Payment to the Month in Monthly Revenue Totals). You could either extend the same Zap or create another.

Triggering on the new record, you would then use a Formatter (Date) to format the date to a custom format (MMM) which will return a 3 character Month. You then have an update step which would then Update the Monthly Revenue Totals field in the original record.

You should also consider what happens next year - it may be better to have Year and Month in the Monthly Revenue Totals table so that the same process can continue beyond December.

You could also use Blocks to give you monthly totals as charts or cross tabs - which may be simpler…

Hope this helps?


#3

Thanks @Julian_Kirkness.

I had been wondering the best way to deal with the year, but now I realise I can do this all in one base without having to create a new base every New Year. Thanks for the lightbulb moment.

I’m in the process of setting up the Zap formatting, like you suggested. I have got it 90% of the way there, but it’s still not quite there. I’ll keep going and see if I can figure it all out. Really appreciate your ideas.


#5

For anyone following along at home, I figured out the Zap and wanted to share it with you.

This is a 5 step sequence (which means you need a paid Zapier account, apparently) and it looks like this…

And then each date formatting template looks like this…

I’ve tested it and it works. Formats the dates perfectly.

Hope this helps.