Garden Gantt

A Gantt chart is a tool for project management, developed by Henry Gantt back near the turn of the last century. It’s a horizontal bar chart that shows start/stop dates for various parts of a project.

In order to help plan my garden, I’ve developed a Gantt chart of my own. You enter the start date and duration, and it displays that information using a horizontal bar on the calendar.

Gantt Chart Calendar

Gantt Chart Calendar

Here’s the file. It’s in .xls format because WordPress won’t allow .ods (but it will allow .odt, go figure).

I based it on the writeup in the Squidoo blog

There’s two steps to building the chart.
Step 1. Write the formulas to define the status of the calendar cells.
Step 2. Define the cell formatting, based on that status.

In Step 1, you begin by numbering the dates across the top. In my case I used weeks from February 1. Then, in a set of columns at the left, you provide the start and stop dates. The duration isn’t actually necessary, but we’ll see how it helps in a minute. Finally, you write a formula in each cell in the calendar area for it to take on a 0/1 value, depending on the relation of the date of that cell to the start/stop dates. The pseudocode formula is:

IF (StartDate<=DateOfThisCell AND DateOfThisCell<=EndDate)
THEN ThisCellValue = 1,
OTHERWISE ThisCellValue = 0

As an example, for cell H11, the LibreOffice spreadsheet formula looks like this:
IF(AND($D11<=H$2, H$2<=$F11),1,0)

In Step 2, you format the cell with the formula so that it shows up white if it’s value is zero, and some other color, say green, if the value is one. That’s a two step process itself.

Step 2.1 is to go to menu item Format/Styles and Formatting and define two new format styles, let’s call them GanttON and GanttOFF. In the first one the cell background and font color are both green. In the second one they are both white. Changing the font color makes the numbers invisible.

Step 2.2 is to tie the style to the cell value. You do that using the Format/Conditional Formatting menu choice. For cells with a value of 1, set the contitional formatting to GanttON. For cells with a value of zero, set the contitional formatting to GanttOFF.

Now we tweak the Start/Time/End cells. Keep in mind that there are two basic approaches to planting — direct seed, and transplanting of seedlings started earlier.

Direct seed is simple. It uses one row. If you are planting in the Spring and want to know the harvest date, then you enter the Start date (say, date of last frost), and growing Time. The harvest (End) is determined by these two numbers: End = Start + Time. If you are planting for a Fall harvest and want it ready before a certain date, then you use Start = End – Time.

For transplants, you need two rows. The first row is to time the seedling growth up to some specific transplant (End) date, so Start = End – Time. The second row is time from transplant to harvest, so End = Start (transplant) + Time.

To tie the two together, you make the Start of the outdoor growth equal to the End of the transplant growth.


Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: