Calculate Work Weeks For Your Visio Shapes

We 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:

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:

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:

click to view larger image

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

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:

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.

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:

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!