Using EVALCELL() in #Visio to calculate Workdays

I recently agreed to calculate the number of workdays between two dates in Visio, without realising how difficult it is. Excel users are lucky because it has the NETWORKDAYS() function, but Visio users are not so fortunate. However, Visio does have the EVALCELL function which can be extremely useful in cases like this. I got the inspiration from one of the answers in StackOverflow for a SQL user who also needed a solution.

This slideshow requires JavaScript.

First, I created a GetDays User-defined Cell row to calculate the number of inclusive days between two dates. This function requires two dates passed through as the start and end arguments.


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


="Get the number of actual days between two dates, eg 1st Jan to 14th Feb : "&EVALCELL(User.GetDays,"s","01 Jan 2018","e","14 Feb 2018")

Second, I created a GetAdjusteDays User-defined cell row to provide the necessary addition of 1 day if the start date falls on a Sunday, and the subtraction of 2 days or 1 days if the end date falls on a Sunday or Saturday respectively.




="Get the number of days between two dates, adjusted for start and end day of the week : "&EVALCELL(User.GetAdjustedDays,"s","01 Jan 2018","e","14 Feb 2018")

Thirdly, 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) between two dates.




="Get the number of work days between two dates, eg 1st Jan to 14th Feb : "&EVALCELL(User.GetWorkdays,"s","01 Jan 2018","e","14 Feb 2018")

This means that I can now include these User-defined Cell rows in any shape that I need to calculate the number of workdays for.

For example, if I have a shape with Start Date and End Date Shape Data rows, I can call either the User.GetDays or User.GetWorkdays functions using the EVALCELL() function.





Of course, I guarded these formulas because I do not want a user to accidently overwrite them.



Leave a Reply

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

You are commenting using your 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.


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

Azure Solutions for Office 365, and more...

Rob Fahrni



Life with Visio and other Microsoft Toys!

Nilsandrey's Weblog

Just another 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: