Rolling Annual Dates, weeks before or after date formula and calendar subscriptions

Heather_Hale · ‎Jul 13, 2023

Apologies in advance if this is elsewhere or common knowledge or the query is posted in the wrong location (Formulas). I did look all around to find a similar Q&A elsewhere (found some close but none exactly what I was looking for), so...

TWO sample use case needs for the following:

1) Gardening: calculating when to sow or transplant a crop indoors or outdoors in relation to estimated frost dates.

2) Annual Events (film festivals, awards shows) that occur at about the same time every year.

Q1: How can I have a date withOUT the year? (reworded: How can I set up ballpark seasonal dates to faciliate copying over every year?)

A-ish? I know I can do three columns (month, day, year) - but then I can't set up an annual calendar subscription, right?

Q2: Is there a way to do a formula to calculate X# of weeks before a key date (i.e.: to add or subtract from the earliest frost date in a column).?

Thank you!

TheTimeSavingCo · ‎Jul 16, 2023

Oh so sorry! Here's a fixed link, and I've also updated my original post so that the link works there too, apologies!

Adam, Consultant @ The Time Saving Co

See Solution in Thread

TheTimeSavingCo · ‎Jul 13, 2023

Q1: How can I have a date withOUT the year? (reworded: How can I set up ballpark seasonal dates to faciliate copying over every year?)

Hmm, you could get a field to display a date without a year via DATETIME_FORMAT() but you plan on actually using that date value to calculate stuff right? Could you provide some examples of what you'd like to do with that date without a year?

Q2: Is there a way to do a formula to calculate X# of weeks before a key date (i.e.: to add or subtract from the earliest frost date in a column).?

Yeap, you can use DATEADD() for that. Not sure if I've understood your usecase though!

Adam, Consultant @ The Time Saving Co

Heather_Hale · ‎Jul 14, 2023

Thank you, Adam!

I tried the DATETIME_FORMAT() but this is a sample of my result: "2024-11-24T00:00:00+00:00"

Maybe I missed a step? Or did something wrong? (It wouldn't let me change the formatting because it didn't recognize the result "type" as a number or a date).

Not sure I understood (or at least correctly applied) the DATEADD() as that seemed to just reflect the same date. (Actually, it gave me the day before but I couldn't tweak it to get any other dates). Again, I'm sure I must be doing something wrong. I did try adding "+7" or "-14" (to add a week or subtract two) but I couldn't seem to get it to work.

Thank you for trying to help! 😉

As per your kind request: here's an example use case:

Let's say I want to plant Arugula (lettuce greens) every year. I can get an early start on (and prolong) the growing season by planting seeds safely in my warm greenhouse "4 weeks before" XYZ Frost date. (There are four different frost dates but for simplicity's sake, let's just use the Average Frost Date (i.e.: when the surrounding area is TYPICALLY safe from any more risk of frost). In my case, that is 3/1. Every year. (Give or take - it's all just historical guesstimates for Mother Nature 😉 ).

In order to give these fledgling seedlings the best chance of survival (and make the highest and best economical use of my time, gardening real estate and resources), on AVERAGE, I can "safely" transplant them outside into the increasingly-less-cold dirt on 3/1 . If I wait three weeks, it's "almost guaranteed" we can safely avoid the risk of frost by waiting 'til after 3/20.

Thus, I'd like a formula that will help me correctly gauge 4 weeks BEFORE these key "risk of frost" dates have passed, alerting me to start sowing seeds in my greenhouse - and allow me to calculate all the crops off whichever columned dates seem to work the best or be the most relevant on any given year or season (or that I can tweak ballpark adjustments on an ongoing basis because every year and season is different - and each crop has different cycles, schedules and needs).

To clarify: if I use the 3/20 "almost guaranteed risk of frost has passed" date, then we could start planting seedlings 2/20 (of every year).

As an use case example in the opposite direction: Corn, Cucumbers, Melons, etc. need to be planted 1-2 weeks AFTER the risk of frost has passed, thus the value to a columned formula that would allow me to adjust annually based on my microclimate and first-hand experience here or wherever I next move to.

Of course, I could do this all in Excel 😉 or jerry rig Airtable - but the end goal is to be able to enable a calendar subscription I can toggle on and off - along with the phases of the moon 😉 - to help me appropriately and effectively ballpark budget my gardening time and effort around professional deliverables and other personal commitments.

Does that all make sense?

THANK YOU!

TheTimeSavingCo · ‎Jul 15, 2023

Ah, yes, thank you very much for the explanation!

I've built something here that I believe should do what you're looking for. There's a bit too much to explain, and if you could duplicate the base, poke around it, have a look at the formulas etc, and then ask me any remaining questions you might have on how it works that'd be great

The idea is to have a "Crop data" table where you set the name of the crop and how many weeks before the frost date you want to start planting it. For crops that you only want to plant after the frost date, we'd just put a negative there. (Not the prettiest, I know, but it works. If we really felt strongly about this we can create a system to handle it though)

In the "Planting Data" table, we'd have a select field to set the year of planting, and we'd have a formula field that takes that "Year of Planting" value and converts it into an actual date with "3 January". If the frost date changes we'd simply modify this formula field.

We then have a linked field to set which crop we're planting, a rollup field to grab that crop's "Weeks before planting" data, and a formula field with "DATEADD()" in it to get the actual date you want to plant