• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Visio Guy

Smart graphics for visual people




  • Home
  • Hire Me
    • Hire Me
    • Résumé
  • Products
    • Products
    • Bubble Revision Shape
    • Layers to Pages Utility
    • Rack Unit Dimension Line
    • Radial Elements Tool with up to 100 Wedges
    • Text on a Circle Visio SmartShape
  • Index
    • Articles by Date
    • YouTube – VisioGuy
    • Download Information
    • Suggestion Box
    • Shop
    • Visio Art
    • Visio Links
    • – Visio Shapes & Stencils
    • – Visio Templates & Drawings
  • About
    • About
    • Donate
    • GitHub
    • jsFiddle
    • Reading List
    • Subscribe & Follow
      • – E-mail
      • – facebook
      • – Twitter
      • – RSS
    • Privacy Policy
  • Discussion Forum
You are here: Home / Development / ShapeSheet / Month Math

Month Math

July 10, 2007 By Visio Guy 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.

  • Tweet
  • More
  • Pocket
  • Share on Tumblr
  • Print
  • Email

Related posts:

  1. Center-sizing Smart Shapes
  2. Choose Colors with Shape Data Fields
  3. Casting Units in Visio’s ShapeSheet
  4. User-defined ShapeSheet Functions in Visio 2007
  5. Text Resizing with ShapeSheet Formulas

Filed Under: ShapeSheet Tagged With: Calendar, Custom Properties, Formulas, Functions, Shape Data, ShapeSheet

Previous Post: « Visio Newsgroups and Discussion Boards
Next Post: Map Distance Dimension Line »

Reader Interactions

Comments

  1. Jonathan says

    July 12, 2007 at 5:12 pm

    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)?

  2. Visio Guy says

    July 13, 2007 at 9:17 am

    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

  3. Jonathan says

    July 13, 2007 at 4:21 pm

    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.

  4. Tuesday says

    May 30, 2008 at 11:11 pm

    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 Reply Cancel reply

Primary Sidebar

Buy Über Bubble Revision Shape
Purchase

Categories

Buy my book!

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Tag Cloud

A/V Artistic Effects BPM Code Connectors Control Handles Countries Custom Patterns Custom Properties Data Graphics Data Linking Data Visualization David Edson David Parker Fill Format Formulas Functions Geometry Gradient Images Links Maps Multi-shapes Network Programming repeating shapes Resources Right-Click Actions Scale Shape Data ShapeSheet ShapeSheet Formulas ShapeSheet Functions SharePoint shiny SmartShapes Sport Sports Text Themes Tools Transparency User-defined Cells Visio 2007 Visio SmartShapes

Top Posts & Pages

  • - Visio Shapes & Stencils
  • - Visio Templates & Drawings
  • Amazon AWS Visio Shapes
  • Sankey Diagram Shapes for Visio
  • Free Visio People Shapes
  • Bubble Revision Shapes
  • AV Engineering Diagrams with Symbol Logic ECAV
  • Release the Power of Visio Custom Line Patterns
  • Crayon Visio Network Shapes, Revisited
  • Text Along a Connector's Path in Microsoft Visio 2010

www.visguy.com - Visio Guy - since 2006

 

Loading Comments...