How to Localize the Agile MSF Iteration Backlog’s Weekends

31 במרץ 2011

If you’re using the Agile MSF process template in TFS 2010, you might be using the built in Iteration Backlog document. If not, you might really want to. It’s a nice and simple to use Excel document, backed up with some macros for calculations (.xlsm file). If you’re already using it, you may have come across the following problem: some days that you mark as planned interruptions from work do not affect your remaining days!

image

This problem occurs in countries where the work-week is not Monday-through-Friday. Notice that the marked Days and Remaining Days are zero. Also, note that the date (03/04/2011 – that’s in dd/mm/yyyy format) is Sunday. In Israel, for example, Sunday is a workday, and should therefore count as one day.

To fix that, you need to change the function that calculates the two fields.

The default function for Days, which applies to the U.S. and countries with a Mon-Fri work week looks like this:

=IF(AND(ISNUMBER([@[Start Date]]),ISNUMBER([@[End Date]]), [@[Start Date]]<=[@[End Date]]),NETWORKDAYS([@[Start Date]],[@[End Date]]),"")

You need to replace the NETWORKDAYS function, with NETWORKDAYS.INTL (international) which accepts an optional parameter for declaring when the weekend is, as follows:

IF(AND(ISNUMBER([@[Start Date]]),ISNUMBER([@[End Date]]), [@[Start Date]]<=[@[End Date]]),NETWORKDAYS.INTL([@[Start Date]],[@[End Date]],7),"")

The added “,7” is the enumerated value of the kind of Weekend. I’ve added the look up table from Excel’s help, for your convenience:

image

Likewise, you need to replace the function for Remaining Days from:

=IF(AND(ISNUMBER([@[Start Date]]),ISNUMBER([@[End Date]]), [@[Start Date]]<=[@[End Date]]),IF(OR(IterationStart>[@[End Date]],[@[Start Date]]>IterationEnd,[@[Start Date]]>[@[End Date]],TODAY()>MIN(IterationEnd,[@[End Date]])), 0, NETWORKDAYS(MAX(IterationStart,[@[Start Date]],TODAY()),MIN(IterationEnd,[@[End Date]]),Holidays[Date])),"")

to:

=IF(AND(ISNUMBER([@[Start Date]]),ISNUMBER([@[End Date]]), [@[Start Date]]<=[@[End Date]]),IF(OR(IterationStart>[@[End Date]],[@[Start Date]]>IterationEnd,[@[Start Date]]>[@[End Date]],TODAY()>MIN(IterationEnd,[@[End Date]])), 0, NETWORKDAYS.INTL(MAX(IterationStart,[@[Start Date]],TODAY()),MIN(IterationEnd,[@[End Date]]),7,Holidays[Date])),"")

That’s all there is to it! Now you can enjoy properly reported Agile development even if your country has different work habits!

Hope this helps,

Assaf.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*