Showing week of the year

[Jumpy]

Hi and welcome to my first post here. I'm from germany and there instead of the Date I often need the week of the year this Date belongs to.
The german word is "Kalenderwoche". Don't know the english expression.

I have the Date in a User defined Cell and show it in the Shape-Text via Insert-Field.
In the Fields Sector of the Shape-Sheet there is a Format Column and using the Formatstrings like "{{dd.MM.yyyy}}" I can format my Date.

Is there a Formatstring to display the week of the year? I tried "{{ww}}", because in the Offline-Help there was a link to Formatstrings, where ww was said to do just what I wished, but it doesn't work. Instead of a formated Date in my Text, I only see "ww".

Thanks for any help you may give me
Jumpy

[wapperdude]

The "{{ww}}" won't work. 

When a date is inserted in a shape, e.g., by using insert > Field, the Text Fields section is added to the shapesheet, and an entry into the Format cell, e.g., FIELDPICTURE(209) is created.  Where the "209" can be any of a variety of code numbers, and determines the format of the displayed entry.  I could not find a listing of the available codes.    Entering the "{{ww}}" as a custom formula, creates a Format cell entry of FIELDPICTURE(0) -->  esc(0), and a Value cell entry of "{{ww}}".   

I could find no reference to "week(s)" and do no believe it is a supported format option.    There is no week or weeks shapesheet function either.     See http://msdn.microsoft.com/en-us/library/ms427031.aspx

Wapperdude

[aledlund]

One of the issues with the 'office help' facilities is that they will often mix VBA and shapesheet discussions, so you have to watch for them. The other thing that will sometimes mislead is that when they mix platform calls (like you start looking for visio and discover your in excel discussions).
al

[Visio Guy]

Servus Jumpy,

I don't think Visio or the ShapeSheet has a way to format a date as week-number. I've rarely seen this in the US, but I know that this is very common in Germany. Just look at my U-bahn weekly ticket: it says "Wochen 43".

I think there might be a fairly simple way to calculate this in the ShapeSheet, however. If I can get it working, I'll publish an article shortly about this.

[Jumpy]

Thanks for your answers so far.
I dimly remember to have seen a formula to calculate the "week of a year" from a given Date in Excel somewhere.
If I can find it again I'll try to adapt it to Visio-Shape-Sheets and post it here.

[Jumpy]

Found it and Converted it to Visio:

=INT((User.Row_1-DATE(YEAR(User.Row_1+3-MODULUS(User.Row_1-2,7)),1,MODULUS(User.Row_1-2,7)-9))/7)

This monster of a formula, copied for example in User.Row_2, yields the number of the week, calculated from a Date in User.Row_1.

The Value (for this week for example) is of the form: 45.000 ed, where ed stands for a duration, i think.

It may be, that it stands for a duration in days, because the calculations leads Visio to asume that, but because of the division through 7 it's weeks actually.

It regards the fact, that in germany the week starts at mondays.

Maybe you can use it one day, when working for a german customer...

Jumpy

[Visio Guy]

Jumpy is too fast!

I was working on this article today but was too slow to publish:

Calculate Work Weeks For Your Visio Shapes

[Jumpy]

I may have been faster, but reading your article, I admit that:

a) your formula is a little bit easier and therefore
b) easier to understant and
c) very good and detailed explained.

Thanks for that,
Jumpy

[patrick0427]

I need to use week numbers in a timeline created from importing Project data.  Using your formula is there a way to add the week number format "ww" to the pull down list for Interim Markers, Milestones, etc.?

[patrick0427]

Anyone?

[Yacine]

Guess it is too specialised.
I don't myself, use Visio timelines. Which templates do you use? And why can't you insert Chris' formulas instead of wanting to use Excel's ww format?

[Jumpy]

Hi,
when I started this thread my final goal was to use the formula on the timeline shape myself, but it was to much effort.
Problem is, that this shape comes with an addon and is recalculated all the time.
So basically what you have to do (using VBA) is after each recalculation to loop through all the sub-Shapes of the timeline shape. Identify the shapes youu want to change. Add the user defined row with a variation of the above formula. Let the textfield show that user.cell.

It is always tedious(?) work to add a functionality to a shape that already has tons of functionality, like the timeline or swimlane shapes. Especially if they come with an addon that is not explained anywhere.

It would be much easier if the ww format would work.

[wapperdude]

Yep!