So far in this series I’ve written 4 articles building our Project Tracker.  I’ve set up the Updates table and created progressively more complex Pivot Tables by using Lookups, Synthetic Data and Bucketization.

But let me stop for just a moment to talk about where I’m going with this.  I’ve written before about Excel Dashboards.  In Stop Monthly Reporting Madness I described a 3-step process for Excel automation: Update, Refresh, Share. This 3-step process works for Trackers and Dashboards and just about any Excel Workbook for managing a fixed dataset that accepts changes over time.  Exactly how you realize this 3-step Mantra really depends on the nature of the underlying data and your own creativity.

Generally, when I create a new Excel project, my objective is to do as much of the work as possible up front, when I’m creating the workbook.  Once completed, the workbook shouldn’t need to be changed, only data added.  The workbook just reliably does what it was setup to do.  Day-in, day-out, as I use the workbook, it is as simple and as automated as possible.  That 3-step process is always my goal: Update, Refresh, Share.

Series Summary

This is the fifth article in my “Excel BTS Project Tracker” series.  In this series, I’m developing a completely functional Project Tracker for Mobile Telecoms using Microsoft Excel.  With each new article in the series, I enhance the previous version of the Tracker and teach you some new Excel techniques at the same time.  If you work in Technology, this tutorial is for you.  If you work in the Mobile RAN, you’ll be able to download the tracker and begin using it with very little modification.

Even if you don’t work in Telecoms, these techniques are a solid foundation for whatever you do with Excel.  If you ever do analysis, make reports, forecast, or theorize, you’ll use these tricks daily.  Your workbooks will blow away your boss and amaze your peers.  People will start to like you and ask you for help.

So far, I’ve covered setting up the basic data table, Pivot Tables, Excel Lookup functions, and using Synthetic Data to bucketize Dates.  In this article, I’ll show you how to synthesize an entirely new date format.  This works great when the projects you track can be completed in less than a month.  This will show more meaningful progress for shorter projects.

You can download my tracker to follow along with this article.   You can use this workbook exactly as it is, or customize it to fit yourexisting projects.

Click here to Download the Tracker

In the previous article, I omitted some discussion to keep the article a manageable length.  In this article, I’ll show how to use Week Numbers as the date Bucket.

You can read that article by clicking the link below. This article uses the same workbook, so no need to download it again.

Excel BTS Project Tracker (Date Buckets)

If you’re new to this series, start with the first article.  In just a few minutes of reading, you can get current with the series and then proceed to read this article. At the end of each article I link to the next article, so you’ll have no trouble navigating through the entire series.

Excel Project Tracker (with the Coolest Excel Function Ever!).

Tracking By Week Numbers

In the previous article I implemented Monthly date buckets. But many Telecoms projects are small in scope, meaning that monthly tracking does not provide enough granularity to track the project effective.  For that, we need to track by weeks.

Tracking by weeks is a challenge because Excel has no built-in number format which includes the week number.  So although we can derive the week number using the WEEKNUM function, WEEKNUM doesn’t return a sortable date like that returned by the DATE function.

There is another problem, too. Imagine the project starts in December, and ends in February.  Being close to the end of the year, December has large week numbers, while February, being near the start of the year,  has small week numbers.

Create our own Date Format

Both these problems can be fixed by prepending the year to the 2-digit week number. In other words, we need to synthesize an entirely new date format, which I dub “YYYY-WW”.  Technically, this is a string, not a date.  But it will sort like a date which is what we want.

Here is the formula to create that date format:

=YEAR(H2)&"-"&TEXT(WEEKNUM(H2,2),"00")

The YEAR function is pretty straightforward. It returns a 4-digit year.

The TEXT(WEEKNUM(H2,2),"00") function is more interesting.  Starting from the inside, WEEKNUM(H2,2) The arguments to WEEKNUM are reference to a date in cell H2, and a return type 2, which says the week begins on Monday.

This formula is embedded in a TEXT function. There are 2 arguments to TEXT function: a string, which in our usage is the week number, and a format string enclosed in quote makes.  I’ve used the format string “00” to force a 2-digit week number to display.

There is just a bit more to the formula.  Recall that an “&” is used to concatenate 2 strings. In the middle of the formula, the “&”-“&” is used to concatenate the year, a hyphen, and the week number. The result has the format YYYY-WW, which will look something like “2014-23”.

You can see all this in the Updates worksheet, column E. The Pivot Table using this date format is on worksheet “Date Bucketization”, rows 3-10. To see the cumulative progress for the project, set up a Data Table for this Pivot like I did in the previous article.  You can see that Data Table in cells B36:P41

Conclusion

In this article, I Synthesized a new Date format to show weeks of the year in proper temporal order.  I used the same Synthetic Data technique I should you in an earlier article, using some simple Excel functions to get the desired result. Once I had that result, I used the same progress summing technique I showed you in the previous article.

Click this link to read the next article in the series.

Excel BTS Project Tracker – Dynamic Named Ranges

Coming Up Next

In the next article in this series, I’ll add Dynamic Named Ranges to the project tracker.  Dynamic named Ranges are conceptually similar to the Named Ranges we already discussed.  Just like the Named Ranges, they make your formulas more readable, like a form of self-documenting code.

But Dynamic Named Ranges are different in an important way. They change their size depending upon the data they span.  Hence, the name “Dynamic”.

Why do we need Dynamic Named ranges?  Remember that my goal for this and most of my Excel projects was a 3-step process to add current data: Update, Refresh, Share.  Dynamic Named Ranges remove a significant impediment to that goal.

One of the biggest impediments is Charts. The chart included in the Project Tracker shows the cumulative progress to date. Whenever I update progress, the chart needs to expand to include the new dates. That’s a manual process that I prefer to avoid. Sure, the chart could be setup to include blank cells that will populate as time goes by.  But that seems inelegant, and if the project lasts long enough, I’ll still need to expand the chart manually.  Dynamic Named Ranges fixes this problem no sweat!

Dynamic Named Ranges can also be used to set the Print Area so that you don’t need to manually set it whenever the data changes. This is another time saver that can also be a source of error.  By automating, we waste less time and eliminate errors.  Outstanding!  See you next time!