Writing a WeekNum(…) function for Visio

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:

Excel Week Num Formulas

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:

VisioWeekNumFormulas

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:

Week Nums in Timelines

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.

Week Nums in Calendars

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

View Week Numbers on Calendars.vsdx

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

JackBinnall

O365 and SharePoint

Simplify Tasks

Want to learn the simple way?

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

John Goldsmith's visLog

be smart, be clear, be visual ...

Mo's blog

Personal views on Dynamics 365 for Operations and Technical Architecture.

Chris Webb's BI Blog

Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

davecra.wordpress.com/

Azure Solutions for Office 365, and more...

Rob Fahrni

I AM FAHRNI

johnvisiomvp

Life with Visio and other Microsoft Toys!

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Smart graphics for visual people

%d bloggers like this: