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.

User.GetDays

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

User.GetDays.Prompt

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

User.GetAdjustedDays

=INT((EVALCELL(User.GetDays,"s",ARG("s"),"e",ARG("e"))+IF(WEEKDAY(DATEVALUE(ARG("s")))=7,1,0)-IF(WEEKDAY(DATEVALUE(ARG("e")))=7,2,IF(WEEKDAY(DATEVALUE(ARG("e")))=6,1,0))))

User.GetAdjustedDays.Prompt

="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, and lastly, I could write the GetWorkdays User-defined Cell row to fully calculate the number of workdays between two dates.

User.GetWorkdays

=(5*INT(EVALCELL(User.GetAdjustedDays,"s",ARG("s"),"e",ARG("e"))/7))+MODULUS(EVALCELL(User.GetAdjustedDays,"s",ARG("s"),"e",ARG("e")),7)

User.GetWorkdays.Prompt

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

Prop.DurationDays.Value

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

Prop.DurationWorkdays.Value

=GUARD(EVALCELL(User.GetWorkdays,"s",Prop.StartDate,"e",Prop.EndDate))

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

 

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 )

w

Connecting to %s

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/

Microsoft Office 365 Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

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

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: