date calculation for plants

[homosapien.tv]

Hello all

I've got a curly one here.

When you pick a plant has a direct bearing on when you plant it (ie. month of the year that you plant).

The following formula works out the planting date given the date entered when the person wants to pick up the plants.

Planting date =

Case( Month(Data Entry Pickup) = 1, Data Entry Pickup - plant_growth::January ,

Month(Data Entry Pickup) = 2, Data Entry Pickup - plant_growth::February ,

Month(Data Entry Pickup) = 3, Data Entry Pickup - plant_growth::March ,

Month(Data Entry Pickup) = 4, Data Entry Pickup - plant_growth::April ,

Month(Data Entry Pickup) = 5, Data Entry Pickup - plant_growth::May ,

So I've got two DB's, one with plant growth times according to time of year (ie. january it might take 20 days, feb 24 etc) and this is deducted from the Date of Pickup entered.

And this works fine.

But I've found it needs to be a lot more specific. Now I have to say that for example if I plant something between January 1 and January 7 it will take 24 days, between the 8th and the 20th it will take 25 days, and between the 21st and the 31st 26 days.

Can anyone help me please?

Best regards

Drew

[Gogargirl]

Sufferin' succotash, Drew!

Now I bet there's a more elegant way to do this, but here's a way that won't mean having to undo *everything* you have already. (Argh!! )

In the plant growth time DB, change those month fields to week fields. Yes, I know you'll have 52 of them, but weeks of the year are constant, whereas the veggies you might want to grow aren't. You'll have over 1,500 growth times to enter - but presumably you want to know that a pea planted in the 2nd week of November takes 42 days to grow and a bean takes only 30.

The calculation for the planting_date in the other DB will need to be chaged to:

Case(WeekofYear(Data_Entry_Pickup) = 1, Data_Entry_Pickup -plant_growth::Week1, WeekofYear(Data_Entry_Pickup) = 2, Data_Entry_Pickup -plant_growth::Week2, etc. up to week 52.

It's a long calc - maybe someone can improve it - but it shouldn't take you too long with a bit of search/replace/cut/paste in a text editor. If you still have the picking_date calc field, you'll need to change that calc too.

Power to your carrots!

[Fitch]

Don't overlook the Choose[] function, it's perfect for any calc that's based on a series of 0, 1, 2, 3 etc. (note that the first value is 0, thus the "" blank):

Choose(WeekofYear(Data_Entry_Pickup), "",

Data_Entry_Pickup - plant_growth::Week1,

Data_Entry_Pickup - plant_growth::Week2,

Data_Entry_Pickup - plant_growth::Week3,

etc. up to week 52.

[Gogargirl]

Bingo Fitch! I knew there was a better calc there somewhere.

[homosapien.tv]

Thanks to you both....Alison you saved me once again and Fitch you streamlined it....cheers...drew