Site icon bVisual

Prototyping Visio ShapeSheet Formulas in Excel

David Parker

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.


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.

The formulas in the above table are:

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:

ExcelVisio
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 FormulaVisio 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:

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:

So the above table has the following formulas:

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

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 found 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/
 
 

Exit mobile version