Hello everyone,
I’m trying to create a crop map where every row represents a vegetable bed on my farm, labeled 1-A-1, 1-A-2…1-B-1, 1-B-2, etc.
In my crop plan table, the location column is linked to to the map table so that I can choose that my second succession of lettuce will go into 1-A-1 and 1-A-2. Another crop may also go into those beds later in the season after the lettuce has been harvested.
My hope for the map table is for each row to represent a bed and for each column to represent a week of the season. When the date of that week is between the planting date and harvesting date of a crop located in that bed, I want the formula to return the name of the crop. I got this basically working by using a lookup column to pull the planting and harvesting dates into the map table, adding columns to convert the dates to numbers, and then writing an If formula for each Week column.
IF(AND({Plant Date to Numeric}<20200521,{Last Harvest Date to Numeric}>20200521),{Crop Plan},“False”)
The problem comes when multiple crops are drawn into the Crop Plan column, representing the multiple successions that will appear in that bed. The crop names are listed separated by commas, as are the planting and harvesting dates. The date conversion column return errors as do the formulas in the Week columns.
I feel like there might be a way to do this by using a third table as reference, but I don’t understand relational databases well enough to imagine that. Or maybe there is more complicated formula syntax that can parse all of this out?
Any help you can provide would be much appreciated!
