Month Math

An associate asked me the other day; “how do you add a number of months to a date?”

I thought this would be simple, because the ShapeSheet supports elapsed-time units in addition to date and time formats.

With elapsed-time, you can manipulate quantities of time, so the problem seemed fairly easy.

Well, not exactly… I found that Visio supports the following elapsed-time units:

• Elapsed weeks (ew)
• Elapsed days (ed)
• Elapsed hours (eh)
• Elapsed minutes (em)
• Elapsed seconds (es)

The letters between the parentheses indicate the unit that you would use in the ShapeSheet. You could indicate 1 week by typing: “7 ed” in a ShapeSheet cell. But to solve our problem, we need Elapsed months, which is not in the list! Luckily, Visio has a bunch of date and time functions that let you work with time and duration.

Let’s re-state the original problem into more precise text:

“Calculate an end date given a start date, and a number of elapsed months”

To do this, we have to use various ShapeSheet date and time functions to tear-down and build-up a new date. I believe the functions you need are very similar to what you would use in Microsoft Excel.

But first, let’s give our shape a few Custom Property or Shape Data fields to hold our data and our calculations:

click to view larger image

Note that Prop.Start and Prop.End are typed as 5 (visPropTypeDate). This allows us to select from a nice calendar control when specifying values, and also offers nice date-formatting in the Custom Properties (Shape Data) dialog boxes. Even though Prop.Start = DATETIME(39083), the dialog box will display “1/1/2007”, or similar, depending on your system’s date-time settings.

Note also that Prop.NoMonths is typed as 2 (visPropTypeNumber). This allows the user to simply enter a number value for the number of months that have elapsed.

Prop.End doesn’t have a value yet, because we need to calculate that from Prop.Start and Prop.NoMonths. We also want to protect Prop.End from being changed by the user, since it is derived from the other two cells.

We can enter one monster of a formula to do the date math, and here it is:

GUARD(DATE(YEAR(Prop.start),MONTH(Prop.start)+Prop.NoMonths,DAY(Prop.start)))

If you look closely, you’ll see that Prop.start gets broken down into year, month, and day components using the functions YEAR, MONTH, and DAY. Then we simply add Prop.NoMonths to the month-component, and build a new date using the DATE function.

The GUARD formula is wrapped around the whole thing to protect the user from overwriting the calculated value. Unfortunately, this doesn’t keep the user from thinking that he’s changing the value; it’s just that after any edits are made in the Custom Properties (Shape Data) dialog box will be discarded.

If you select the shape we’ve built in this example, the Custom Properties (Shape Data) dialog will appear as follows:

Since the user can freely edit the End Date value, I’ve added the “(Read-only)” text to remind them that this is not a field to be messed with. If the user enters new text in the field, it will revert to the calculated value when that text-field loses focus. “Read-only” might help the user to understand why this happened and lessen the surprise!

A shape that is more clearly understood might break down the calculation in User-cells as shown:

You can see that it’s easier to spot the GUARD formula in Prop.End when all the calcualtions are up in the User-defined Cells section. The DATE function in the User.endcalc cell is also much clearer to read.