I have no idea how to make the title succinct and easy to understand, but here's what I want to do.
I run a market garden. I am building a crop planning tool inside airtable.
Throughout the year, the typical timings of events for different crops will change.
For this example, we'll use my Weeks to Harvest calculation. In peak summer, lettuce will be ready to harvest 4 weeks after transplant. In the darkest part of winter, it'll take 8 weeks.
Now, there is not a cut off date where suddenly it goes from taking 4 weeks to 8 weeks.
So I want to be able to store the following data against a crop
1) Fastest weeks to harvest (peak summer) - e.g. 4
2) Slowest weeks to harvest (dark winter) - e.g 8
3) Fastest date of the year (based on transplant date) e.g. Jan 1
4) Slowest date of the year (based on transplant date) e.g. June 1
And then I want to make a formula field that will adjust the weeks to harvest based on the time of year of transplant. This can be a linear relationship. So if we transplant lettuce on:
1) Jan 1, the Weeks to Harvest prediction will be 4
2) Jun 1, the Weeks to Harvest prediction will be 8
3) April 1, the Weeks to Harvest prediction will be 6.
It seems simple and complicated at once. Does anyone have any ways of going about this formula?
In all likeliness, the relationship should probably be treated as a curve, but figuring out the shape of that curve is beyond me right now!
