Prototyping #Visio ShapeSheet Formulas in #Excel

I am working on an application where the horizontal axis of the Visio page represents dates from left to right. In fact, the each of the fixed horizontal grids are 1 day, and I need to have shapes that understand the begin date at the left edge of the shape, and the end date at the right edge of the shape. There is, therefore, a number of elapsed days representing by the width of the shape. However, the underlying grid can either represent all days, or it can be changed to only represent weekdays by omitting the weekends. I struggled to find the best formula to calculate the elapsed days or weekdays between two dates in Visio ShapeSheet formulas, so I turned to Excel to provide an inspiration. The Visio ShapeSheet is modelled on the Excel worksheet, and formulas can be entered into the cells in much the same way. However, the available functions differ since Excel is mainly used for arithmetic and statistics, but Visio is used for graphics and data. In this article, I demonstrate how I used C# and Excel to construct and test formulas for use in a Visio shape.

This slideshow requires JavaScript.

The functionality of similar named functions in Excel and Visio can also be subtley different. For example, each application has a WEEKDAY() function, but the Excel WEEKDAY() function has an optional second argument to specify how the number is returned, whereas the Visio WEEKDAY() function only returns a number starting with 1 for Monday.

Getting the number of weekdays between two dates

Excel has a NETWORKDAYS() function that can return the number of weekdays between two dates, but unfortunately Visio does not. However, this could provide me a useful check that any formula that I wrote does return the right value. So my plan was to write an Excel formula that could be replicated in Visio, and can be tested against the NETWORKDAYS() function.

A web search eventually found a C# method that returns the number of weekdays between two dates:

public int Weekdays(DateTime dtmStart, DateTime dtmEnd)
{
     // This function includes the start and end date in the count if they fall on a weekday
     int dowStart = ((int)dtmStart.DayOfWeek == 0 ? 7 : (int)dtmStart.DayOfWeek);
     int dowEnd = ((int)dtmEnd.DayOfWeek == 0 ? 7 : (int)dtmEnd.DayOfWeek);
     TimeSpan tSpan = dtmEnd - dtmStart;
     if (dowStart <= dowEnd)
     {
          return (((tSpan.Days / 7) * 5) + Math.Max((Math.Min((dowEnd + 1), 6) - dowStart), 0));
     }
     return (((tSpan.Days / 7) * 5) + Math.Min((dowEnd + 6) - Math.Min(dowStart, 6), 5));
}

I then translated this method into an Excel formula that returns the same number as the NETWORKDAYS() function.

So, I constructed an Excel table with Begin Date and End Date columns and a few other columns to provide test formulas based on those dates.

Excel Date Range Test

The formulas in the above table are:

  • Begin Weekday=WEEKDAY([@[Begin Date]],2)
  • End Weekday=WEEKDAY([@[End Date]],2)
  • Elapsed Days= INT( ([@[End Date]]+1)-[@[Begin Date]])
  • Network Days=NETWORKDAYS([@[Begin Date]],[@[End Date]])
  • Week Days=IF(WEEKDAY([@[Begin Date]],2) <=WEEKDAY([@[End Date]],2),  (FLOOR.MATH(INT( [@[End Date]]-[@[Begin Date]])/7)*5)+ MAX((MIN((WEEKDAY([@[End Date]],2)+1),6) -WEEKDAY([@[Begin Date]],2)),0),(FLOOR.MATH(INT( [@[End Date]]-[@[Begin Date]])/7)*5) + MIN((WEEKDAY([@[End Date]],2)+6) -MIN(WEEKDAY([@[Begin Date]],2),6),5))

The equivalent formula in Visio needs to evaluate the days between two dates stored in the Begin Date and End Date Shape Data rows. This is where the EVALCELL() function is useful because it can be used to evaluate the formula entered into another cell passing through arguments.

For example, if I have a User-defined cell, named GetDays, with the formula that expects an End Date argument called “e”, and a Begin Date argument called “s”.

=INT(((DATEVALUE(ARG("e"))+1)-DATEVALUE(ARG("s")))/1 ed)

Then this can be evaluated using the formula:

=EVALCELL(User.GetDays,"s",Prop.BeginDate,"e",Prop.EndDate)

Similarly, I transposed the Week Days Excel formula into a Visio User-defined Cell called GetAdjustedDays as follows:

=IF(WEEKDAY(ARG("s"))<=WEEKDAY(ARG("e")),(FLOOR(INT(ARG("e")-ARG("s"))/7)*5)+MAX((MIN((WEEKDAY(ARG("e"))+1),6)-WEEKDAY(ARG("s"))),0),(FLOOR(INT(ARG("e")-ARG("s"))/7)*5)+MIN((WEEKDAY(ARG("e"))+6)-MIN(WEEKDAY(ARG("s")),6),5))/1 ed

The above formula need to have the following replacements between Excel and Visio:

Excel Visio
WEEKDAY([@[Begin Date]],2) WEEKDAY(ARG(“s”))
WEEKDAY([@[End Date]],2) WEEKDAY(ARG(“e”))
[@[Begin Date]] ARG(“s”)
[@[End Date]] ARG(“e”)

Since the long formula is not very easy to read, I have broken it down into a table showing the original Excel formula parts and the equivalent in Visio:

Excel Formula Visio Formula
=IF(WEEKDAY([@[Begin Date]],2) <=WEEKDAY([@[End Date]],2), =IF(WEEKDAY(ARG(“s”))<= WEEKDAY(ARG(“e”)),
(FLOOR.MATH(INT( [@[End Date]]-[@[Begin Date]])/7)*5) (FLOOR(INT(ARG(“e”)-ARG(“s”))/7)*5)
+ MAX((MIN((WEEKDAY([@[End Date]],2)+1),6) +MAX(MIN(WEEKDAY(ARG(“e”))+1,6)
-WEEKDAY([@[Begin Date]],2)),0), -WEEKDAY(ARG(“s”)),0),
(FLOOR.MATH(INT( [@[End Date]]-[@[Begin Date]])/7)*5) (FLOOR(INT(ARG(“e”)-ARG(“s”))/7)*5)
+ MIN((WEEKDAY([@[End Date]],2)+6) +MIN((WEEKDAY(ARG(“e”))+6)
-MIN(WEEKDAY([@[Begin Date]],2),6),5) -MIN(WEEKDAY(ARG(“s”)),6),5)
) )/1 ed

The Visio shape can now understand both the number of elapsed days, and the number of weekdays between two dates, when the underlying horizontal grid displays both weekdays and weekends:

Visio Days With Weekends

So the above handles the case where I need to get the number of weekdays between two dates, but it does not tell me the weekday which is offset by a number of grids from a given date.

Getting a workday given a number of grids offset from a date

Excel has a WORKDAY() function that returns the weekday date given a date and number of week days offset. Visio does not have a similar function, so I had to do a web search again, and found a suitable c# alternative to calculate Adding Days to a Date but Excluding Weekends:

public static DateTime AddWorkdays(DateTime source, int workdays)
{
 var dayOfWeek = 0;
 if (workdays < 0 )
 {
  dayOfWeek = ((int)source.DayOfWeek - 12) % 7;
 }
 else
 {
  dayOfWeek = (((int)source.DayOfWeek + 6) % 7);
 }
 if (dayOfWeek == 6)
 {
  workdays = workdays - 1;
 }
 if (dayOfWeek == -6)
 {
  workdays = workdays + 1;
 }
 return source.AddDays(workdays + ((workdays + dayOfWeek) / 5) * 2);
}

The above method handles cases where the number of days offset can be negative, but it do not need this for my shape, so I reduced this method to:

public static DateTime AddWorkdays(DateTime source, int workdays)
{
 var dayOfWeek = ((int)source.DayOfWeek + 6) % 7;
 workdays = dayOfWeek  == 6 ? workdays -1 : workdays;
 return source.AddDays(workdays + ((workdays + dayOfWeek) / 5) * 2);
}

I translated this into an Excel formula in the Get Work Date column below, so its results could be tested against the inbuilt WORKDAY() function:

Excel Date Range Test 2

So the above table has the following formulas:

  • End Date=[@[Begin Date]]+[@Grids]-1
  • Workday=WORKDAY( [@[Begin Date]],[@Grids]-1)
  • Get Work Date=[@[Begin Date]] + (IF(MOD((WEEKDAY([@[Begin Date]],2)+6),7 )=6,[@Grids]-2,[@Grids]-1)  +  FLOOR.MATH((IF(MOD((WEEKDAY([@[Begin Date]],2)+6),7 )=6,[@Grids]-2,[@Grids]-1)  + MOD((WEEKDAY([@[Begin Date]],2)+6),7 )) / 5) * 2)

The last formula can then be transposed into a Visio ShapeSheet formula in a User-defined cell called GetWorkDate:

=ARG("s")+(IF(MODULUS((WEEKDAY(ARG("s"))+6),7)=6,ARG("n")-2,ARG("n")-1)+FLOOR((IF(MODULUS((WEEKDAY(ARG("s"))+6),7)=6,ARG("n")-2,ARG("n")-1)+MODULUS((WEEKDAY(ARG("s"))+6),7))/5)*2)

This can then be called by formulas in other cells like this:

=EVALCELL(User.GetWorkDate,"s",Prop.BeginDate,"n",User.GridsWide)

Again, this is hard to digest in one line, so I have broken it down into the following table:

Excel formula Visio formula
=[@[Begin Date]] + =ARG(“s”)+
( (
IF(MOD((WEEKDAY([@[Begin Date]],2)+6),7 )=6, IF(MODULUS((WEEKDAY(ARG(“s”))+6),7)=6,
[@Grids]-2,[@Grids]-1) + ARG(“n”)-2,ARG(“n”)-1)+
FLOOR.MATH(( FLOOR((
IF(MOD((WEEKDAY([@[Begin Date]],2)+6),7 )=6, IF(MODULUS((WEEKDAY(ARG(“s”))+6),7)=6,
[@Grids]-2,[@Grids]-1) + ARG(“n”)-2,ARG(“n”)-1)+
MOD((WEEKDAY([@[Begin Date]],2)+6),7 ) MODULUS((WEEKDAY(ARG(“s”))+6),7)
) / 5) * 2) )/5)*2)

The Visio shape can now understand both the number of elapsed days, and the number of weekdays between two dates, even if the underlying horizontal grid does not display weekends:

Visio Days Without Weekends

 

Conclusion

My old friend Ian van White at Microsoft often asks people which Microsoft application is most like Visio, and, of course, the answer is Excel. This article shows just how similar they are. IN this case, I foukd the similarities a great help in constructing and testing complicated formulas.

By the way, if you want to see where these smart shapes are used, please checkout TimeTable from BuildFore.

Also checkout this article in a similar vein by my good friend John Goldsmith : Using Excel to build Visio ShapeSheet formulae

Another Visio MVP friend of mine, Chris Roth – no less than the VisGuy himself, has more related ShapeSheet formulas here : Calculate work weeks for your visio shapes/

 

 

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...

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: