Share:

" /> Visio Guy » Month Math
Home » ShapeSheet

Month Math

Submitted by on July 10, 2007 – 3:14 am | | 19605 views 4 Comments

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:

Shape Data Fields

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:

Date fields

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:

Calculation Break-down

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.

4 Comments »

  • Jonathan says:

    I can see why Visio would not include a built-in elapsed month unit. How do you define an elapsed month? From June 1st to July 1st is 30 days. From February 1st to March 1st is 28, or 29 on a leap year. Do you say that a month is the time between dates with the same number? Or do you use a “canonical” month that is 30.4375 days long (that’s 365 *3 + 366 for the number of days in a four year period, divided by 48 for the number of months in the same period)?

  • Visio Guy says:

    Jonathan,

    If your landlord tells you that your rent is due on the 1st of every month, do you give him the same answer? :)

    But seriously, I can see that there would be a problem if someone says, for example; “add 1 month to August 31″, because that would be September 31, which doesn’t exist.

    Thanks for the comment!

    – Chris

  • Jonathan says:

    It really bugs me how I only get 28 days of rent in February for the price of 30. Then again, I get paid the same salary during February, too, so I guess it all evens out in the end.

  • Tuesday says:

    Does anyone know how to set the default format for a customized milestone, stored in a stencil file?

    I am looking for a way to store/transform the date format to “{{M/d}}” for all new milestones I add, but can’t seem to figure out how to do it with styles. Now investigating adding a text transform to the ShapeSheet.

    Any help would be welcome. :)

Leave a comment!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.

*