• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

bVisual

  • Home
  • Services
    • How Visio smartness can help your business
    • Visio visual in Power BI
    • Visio Consulting Services
    • Visio Bureau Services
    • Visio Training and Support Services
  • Products
    • Visio Shape Report Converter
    • SS Plus
    • LayerManager
    • visViewer
    • Metro Icons
    • Rules Tools for Visio
    • The Visio 2010 Sessions App
    • Multi-Language Text for Visio
    • Document Imager for Visio
    • multiSelect for Visio
    • pdSelect for Visio
  • Case Studies
    • Case studies overview
    • Using Visio in Education for GIS
    • Visualizing Construction Project Schedules
    • Visio Online Business Process Mapping
    • Nexans Visio Template
    • CNEE Projects, WorldCom
    • Chase Manhattan Bank
  • News
    • Recent news
    • News archive
  • Resources
    • Articles➡
      • ShapeSheet Functions A-Z
      • Comparing Visio for the Web and Desktop
      • Customising Visio Shapes for the Web App
      • Key differences between the Visio desktop and web apps
      • Using the Visio Data Visualizer in Excel
      • Using Visio in Teams
      • Creating Visio Tabs and Apps for Teams with SharePoint Framework (SPFx)
      • Designing Power Automate Flows with Microsoft Visio
      • Innovative uses of Visio Lists
    • Webcasts ➡
      • Visio in Organizations
      • My session and other Visio sessions at MSIgnite 2019
      • Power up your Visio diagrams
      • Vision up your Visio diagrams
      • The Visio 2010 MVP Sessions
    • Visio Web Learning Resources
    • Books➡
      • Visualize Complex Processes with Microsoft Visio
      • Mastering Data Visualization with Microsoft Visio
      • Microsoft Visio Business Process Diagramming and Validation
      • Visualizing Information with Microsoft Visio
  • Blog
    • Browse blog articles
    • Visio Power BI articles
    • Visio for Web articles
    • A history of messaging and encryption
  • About us
    • About bVisual
    • Testimonials
    • Bio of David Parker
    • Contact Us
    • Website Privacy Policy
    • Website terms and conditions
    • Ariba Network
You are here: Home / Visio / Prototyping Visio ShapeSheet Formulas in Excel

Published on February 7, 2019 by David Parker

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.


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));
}
Excel Date Range Test

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:

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

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
Visio Days With Weekends

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);
}
Excel Date Range Test 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:

  • 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 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)
Visio Days Without Weekends

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/
 
 

Related

Filed Under: Excel, Shape Data, ShapeSheet Formulas, Visio Tagged With: Excel, ShapeSheet

About David Parker

David Parker has 25 years' experience of providing data visualization solutions to companies around the globe. He is a Microsoft MVP and Visio expert.

Reader Interactions

Trackbacks

  1. Using EVALCELL() in #Visio to calculate Workdays | bVisual - for people interested in Microsoft Visio says:
    September 11, 2019 at 10:00 am

    […] I used the formula from my earlier article,Prototyping #Visio ShapeSheet Formulas in #Excel , the GetWorkdays User-defined Cell row to fully calculate the number of workdays (weekdays) […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

  • LinkedIn
  • Twitter

Recent Posts

  • Fixing dimensions of 2D shapes
  • Merging Linked Data from Similar Tables
  • Smart Radio Buttons and Check Boxes in Visio
  • Using Button Face Ids in Visio
  • Grid Snapping Revisited

Categories

Tags

Accessibility Add-Ins Connectors Containers Data Export Data Graphics Data Import Data Visualizer Educational Excel GraphDatabase Hyperlinks Icon Sets JavaScript LayerManager Layers Legend Link Data to Shapes Lists MSIgnite MVP Office365 Org Chart PowerApps PowerBI PowerQuery Processes Setup and Deployment Shape Data Shape Design ShapeSheet ShapeSheet Functions SharePoint 2013 SQL Teams Validation VBA Video Visio Visio 2007 Visio for the Web Visio Online Visio Services Visio Viewer Webinar

Footer

bVisual Profile

The UK-based independent Visio consultancy with a worldwide reach. We have over 25 years experience of providing data visualization solutions to companies around the globe.

Learn more about bVisual

  • Amazon
  • E-mail
  • Facebook
  • LinkedIn
  • Twitter
  • YouTube

Search this website

Recent posts

  • Fixing dimensions of 2D shapes
  • Merging Linked Data from Similar Tables
  • Smart Radio Buttons and Check Boxes in Visio
  • Using Button Face Ids in Visio
  • Grid Snapping Revisited

Copyright © 2025 · Executive Pro on Genesis Framework · WordPress · Log in