Share:

" /> Visio Guy » Calculate Work Weeks For Your Visio Shapes
Home » ShapeSheet

Calculate Work Weeks For Your Visio Shapes

Submitted by on November 3, 2009 – 1:43 pm | | 17006 views 4 Comments

work-weeks-in-visioWe all know the numbers of the months. January is Number One! July is the 7th month, and December is the 12th.

But some folks need to know which week it is, and Visio has no formatting option to show dates as indexed weeks.

So we’re here to tell you how to do it!

Weeks in the Calendar

Everyone knows there’s 52 weeks in a year. Well, actually 52.179 weeks if you’re being persnickety and divide 365.25 by 7. But did you know that you can actually have 53 weeks in a year?

If you look at a calendar, it makes more sense:

weeks-example

You’ll notice that the 1st of January doesn’t fall on Monday all that often, and our Monday-starting weeks can spill into the next year. That’s how we can end up with 53 week-rows in a year’s calendar. The first and last weeks include days from the previous and next years, but there are still only 52 seven-day periods in a year.

Since I primarily create software and web-sites, and don’t do much real work, I’m not exactly sure why people need to use numbered weeks. I they could be helpful for scheduling, since a work week always has five work-days, and you don’t have to be confounded by variable-length months.

A quick search of the web reveals a few names for calendar weeks: Which term do you use?

  • Work week
  • Calendar week
  • Week of year
  • Kalenderwoche (German)

The reason I included Kalenderwoche is because I live in Munich and am amazed at how often the Germans talk about which week it is. I wouldn’t be surprised if they actually know what week it is, and in what week something will happen: I vill be taking two veeks facation starting on veek 32…

And whenever I buy a week-long-pass for the U-Bahn (subway), it actually tells me for which week my ticket is good:

wochenkarte

To translate: “41.Woche 09″ means “the 41st week of 2009″!

I should also mention that Jumpy asked the question in the Visio forum about how to do calculate calendar weeks, and he mentioned kalenderwoche, so that further supports my observation. Thanks for the article idea, Jumpy!

If it’s good enough for the Germans, then it’s good enough for you! So keep reading this article…

Insert the Current Work Week the Easy Way

If you want to show the current week directly in a shape’s text, just copy this formula…

FLOOR( ( DAYOFYEAR( NOW() ) + WEEKDAY( DATE( YEAR( NOW() ), 1, 1 ) ) – 2 ) / 7 ) + 1

…and paste it into the Insert > Field dialog, after choosing Custom Formula from the Category list:

insert-calendar-week-now-dialogclick to view larger image

You’ll end up with something like this (the custom formula was inserted after the equals sign):

insert-calendar-week-now-shape

While this works just fine, the NOW() bits in the formula update quite often, which may or may not be desirable. Every time you open your Visio document, this field will update. This makes it great for a title-block element in a template that you use often.

But if you are illustrating date-related themes, you’ll likely want to tie the date to some sort of data stored in the shape, such as a Shape Data field.

Data Driven Dates

You can enter dates in a shape if you create a date-type Shape Data field. This lets us set dates in the Shape Data Window using Visio’s nifty calendar control:

single-shape-data-date-field

We can replace NOW() in the previous formula with a reference to our date-containing Shape Data field, which happens to be named: Prop.date. The new formula looks like this:

FLOOR( ( DAYOFYEAR( Prop.date ) + WEEKDAY( DATE( YEAR( Prop.date ), 1, 1 ) ) -2 ) / 7 ) + 1

You add the field to the shape by selecting the shape, then choosing Data > Shape Data. In the Define Shape Data dialog you can set the field type to “Date”. You can also set the Label and Name of the field.

Users see the Label in the Shape Data window, and developers use the Name in the form: Prop.Name to create ShapeSheet formulas.

define-date-shape-data-field

Note: you won’t see the Name field unless you have configured Visio to be in Developer Mode.

Breaking Down the Calculation in the ShapeSheet

In order to develop the big long formulas mentioned above, I initially created several calculation steps inside of User-cells. If you’re interested in how the formula works, and are curious about several ShapeSheet date-related formulas, you might find this section interesting.

First, I set up a shape that had three Shape Data fields: Prop.year, Prop.month and Prop.day. I could then easily enter numbers for each field in the Shape Date Window to specify a complete date:

It looked something like this:

shape-data-date-fields

Once the shape had date data, I could work with it inside the ShapeSheet. You’ll be interested to know about four date-related ShapeSheet functions that we used to do the calculations:

  • DATE – builds a date from year, month and day arguments
  • WEEKDAY - returns a number that tells you the day of week for the date. 1 = Monday, 7 = Sunday
  • YEAR - tells you the year-component of a date
  • DAYOFYEAR – tells you which day of the year a date lands on

The broken-down steps for getting the work week look like this:

User.inputDate =DATE( Prop.year, Prop.month, Prop.day)
…builds a date from the Shape Data fields that contain year, month and day

User.weekday1 = WEEKDAY( DATE( YEAR( User.inputDate ), 1, 1 ) )
…finds the weekday-number of the first day of the input year

User.dayOfYear = DAYOFYEAR( User.inputDate ) + User.weekday1 – 1
…bumps the input-day forward according to the overlap with last year

User.weekNumber = FLOOR( ( User.dayOfYear-1 ) / 7 ) + 1
…calculates the week number, which is what we were after in the first place!

Once I understood how to calculate the work week, I was able to consolidate all of these cells into one big, long formula. Easier to implement, but harder to understand. You can decide whether you want to use one long formula or several shorter ones.

The calculations I did assume the week begins on Monday. If you want your calendar weeks to start on Sunday, it might be easier to experiment with the broken-down formulas, then consolidate once you’ve got it working properly.

Work Week Resources

So now you’re an expert at calculating calendar weeks. You understand the theory and got 100 on the test. So why waste time doing it yourself when someone else already has?

If you use calendar weeks for scheduling, you might be interested in the 2009 Work Week Calendar templates on offer from The Closet Entrepreneur. You can download PDF and Excel formats.

And if you want to quickly look up the work weeks in a particular month, this site will help.

Or you could just make a smart shape that knows what week it is and use it as a tool to look up work weeks!

4 Comments »

  • Steven Healey says:

    Week numbering is discussed in the ISO 8601 standard. It is used extensively in manufacturing environments, where customer order/shipping cycles do tend to cluster around week boundries (for any process that takes more than 2 days to complete anyway) and where a scheduling/purchasing granularity of 1 day is too fine to be practically usable.

    Note that whether a week runs Monday-Sunday or Sunday-Saturday depends on the nation and entity using the numbering system, which means there are variations on the strict ISO 8601 standard.

  • Jake says:

    I was trying to make this work but every time I copied the code into the Custom Formula field I get an “Error in Formula” message. I am relatively new to Visio so it’s quite possible that I’m doing something wrong. Has anyone else had this problem or can someone point out what I might be doing wrong here?

  • Visio Guy says:

    Hi Jake,

    Do any of the formulas that aren’t working for you contain “User.” or “Prop.” cells that you haven’t yet created in the shape?

  • Gunnar says:

    Hi

    I have managed to show the weeknumber on a simple shape. but how do you put it on a timeline? I do want the interim markers to show the week number.

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.

*