I needed to display the week number of dates in a Visio project of mine, but there is no WeekNum(…) function built-in, so I had to write one, and allow for the date that the week numbers begin in to be varied from 1st January. I also needed to allow for the week numbers to go backwards from the specified week number begin date.
Excel has a WeekNum(..) function, which can take an optional parameter for the day of the week to begin on, and an IsoWeekNum(..) function, and the following table shows the values change for the first 35 days of the year:
What this graphic shows is that the Excel IsoWeekNum(<date>) function returns the same value as WeekNum(<date, 11) and WeekNum(<date>,21) functions.
Excel defaults to weeks beginning on a Sunday, but Visio defaults to weeks beginning on a Monday, as follows:
| Day | Excel Weekday(<date>) | Visio Weekday(<date>) |
| Sunday | 1 | 7 |
| Monday | 2 | 1 |
| Tuesday | 3 | 2 |
| Wednesday | 4 | 3 |
| Thursday | 5 | 4 |
| Friday | 6 | 5 |
| Saturday | 7 | 6 |
A formula can be written in the Visio ShapeSheet to return the weekday number starting on Sunday, as follows:
=MODULUS(WEEKDAY(<date>),7)+1
However, in my test Visio shape, I created a fixed list that is in the order starting with Sunday, so the index of the selected day is from 0 to 6, using the formula:
=LOOKUP(Prop.WeekBeginsOn,Prop.WeekBeginsOn.Format)
This test Visio shape has some User-defined Cells and Shape Data rows so that I can enter/select a date, configure the options, and get the week number:
So, I can select the start day of the week for the week numbers, Week Begins On, and the date which is defines the first week, I can also configure Reset Annually to be TRUE if the numbers are always from 1 to 52, like Excel, or FALSE if the numbers are 1, 2, etc forwards from the date, and -1,-2, etc backwards from the date.
The User.WeekNumStartsOn formula is :
=Prop.BeginWeekDate-MODULUS(WEEKDAY(Prop.BeginWeekDate),7)+User.WeekBeginsOn
The guarded formula in Prop.WeekNum displays the week number accordingly:
=IF(Prop.ResetAnnually, MODULUS(INT((Prop.Date-User.WeekNumStartsOn)/7 ed)+ (Prop.Date>=User.WeekNumStartsOn),52)+NOT(Prop.Date>=User.WeekNumStartsOn), INT((Prop.Date-User.WeekNumStartsOn)/7 ed)+(Prop.Date>=User.WeekNumStartsOn) )
This could be abstracted even more by moving the code into a User-defined cell, say User.WeekNum, with the formula:
=IF(ARG("r"),
MODULUS(INT((ARG("d")-ARG("s"))/7 ed)+
(ARG("d")>=ARG("s")),52)+NOT(ARG("d")>=ARG("s")),
INT((ARG("d")-ARG("s"))/7 ed)+(ARG("d")>=ARG("s"))
)
where “r” is the reset annually value, “d” is the target date, and “s” is the date that week numbers start.
Then the Prop.WeekNum formula can be:
=GUARD(EVALCELL(User.WeekNum, "r",Prop.ResetAnnually,"d",Prop.Date,"s",User.WeekNumStartsOn))
So, how can this be used? Well, in this following example, I added these formulas to the Diamond milestone master in a Timeline diagram. I set the week numbers to begin on 1st July 2019, and you can see the effect of resetting the week numbers automatically or not:
I used Data Graphics to display the Prop.WeekNum values above each milestone.
View Week Numbers on Timelines.vsdx
Update : Well, not for the first time, I find that my friend, and fellow Visio MVP, Chris Roth has previously solved a similar problem, but without the variable start date, so also check out Calculate work weeks for your visio shapes/
So I added my formulas to the Month, Thumbnail month, Week, Multiple week masters in the document stencil. I had to add in a more advanced technique of pushing the formulas into the Prop.StartWeekNum and Prop.EndWeekNum cells on a some of these because a Microsoft add-on updates the dates using the right mouse Configure… option, which was not refreshing the formula. I also found that I had to re-apply the Data Graphics to the shapes if I used the Configure... dialogue.
I have provided the document for download below so the User.WeekNumsTriggers formula can be seen in action, eg:
=DEPENDSON(Prop.StartDate,Prop.EndDate)+ SETF(GetRef(Prop.StartWeekNum), "=GUARD(EVALCELL(User.WeekNum,""r"",Prop.ResetAnnually,""d"",Prop.StartDate,""s"",User.WeekNumStartsOn))")+ SETF(GetRef(Prop.EndWeekNum), "=GUARD(EVALCELL(User.WeekNum,""r"",Prop.ResetAnnually,""d"",Prop.EndDate,""s"",User.WeekNumStartsOn))")