Using Microsoft® Office Project’s
Built-in Functions in Formulas
Advanced Formula Techniques for MS Project Experts
Second Edition - Released in December 2019 - 190 pages
Available to purchase as ebook in pdf format
Download & read on your computer now...
What is in the eBook ?
The eBook is composed of two parts; the Function Reference that explains how to use all the built-in functions listed under the Project category and the Formula Toolbox that contains a set of formulas developed by using these functions and the other built-in functions.
In the Function Reference part, we will get into the details of all these powerful functions and explore their lesser-known capabilities. This part of the eBook can be used as a reference manual for the functions along with the colorful Function Reference Sheet that accompanies the eBook.
Each section in the Formula Toolbox part explains all the steps of the formula development process in detail. Some advanced formula techniques are implemented in these sections. You can add the formulas developed in this part to your formula toolbox and start to use them right away while working on the project schedules.
Who should read ?
This eBook is right for you if you already have knowledge and skills to develop and maintain the custom field formulas in MS Project's any standalone desktop version/edition supporting the feature. Otherwise, it is recommended to first study the eBook Mastering Custom Field Formulas in Microsoft® Office Project.
Why buy the eBook ?
As an MS Project user who already has knowledge and skills to develop and maintain custom field formulas, you can create powerful formulas by utilizing the formula techniques and the features of the built-in Project functions discussed in detail in this eBook. Review the table of contents and the introduction section of the eBook here.
Some new interactive task filters were added to the product, starting from MS Project Standard/Professional 2013. If you are currently using a version of the product earlier than 2013, you won't be able to use these handy filters in your project plans since the new features utilized do not exist in the earlier versions. The formulas working similar to these filters have been developed in the eBook in order to help the users of the earlier versions benefit from the similar functionality. Also, by the help of these formulas, the users of the versions 2013 and later will be able to create a dashboard of the indicators displaying the results of the same conditions tested as these built-in interactive filters. The following formulas are developed in the eBook: Milestones Due in the Current Month, Tasks with No Actuals, Due This Week and Starting Soon.
The custom field formulas are mostly used to process projects' date and time information. Various date range formulas developed in the eBook will quickly show you the tasks whose dates fall into any particular date range specified. The following picture is from the test file of the custom date range formula used to identify the next two weeks' tasks (that is, the tasks taking place in the next two week period following the status date's week) in both the table and the graph part of a Gantt Chart view in all desktop versions/editions:
The section Display Local Settings shows how to create a table and a graphical report (the report is for the versions 2013 and later) to list and print the settings for Project Calendar, Week starts on, Fiscal year starts in, Default start/end time, Hours per day/week, Days per month of any active project plan file (see the screenshots from the test file below).
The following screenshot from the test table shows the results from the week and quarter formulas developed in the eBook for all desktop versions/editions:
The formulas developed to calculate the fiscal information in the section Calculating Fiscal Information will enable you to see the same fiscal information on the tables as the timescale which shows fiscal weeks, quarters and years. The purpose of these formulas is not to implement different models for the fiscal year, but instead, to have a consistent look between the fiscal information shown on the timescale and the corresponding information displayed on the task date fields of a task table. You can filter, group or sort any project's task data based on the fiscal weeks, quarters and years calculated by the formulas. Visit the sample pages here which explain how MS Project’s fiscal year feature works in detail. The following screenshot from the test table shows the results from the fiscal formulas developed in the eBook for all desktop versions/editions:
Note that the fiscal timescale of the Gantt Chart view below displays the same fiscal information as those already calculated on the table above for the milestone of May 6th, 2019 (the fiscal year starts in October):
Besides having a huge set of formulas to use in your work, you will also gain a better understanding of how MS Project's interactive filters and the fiscal year settings work by reading this eBook.
Also see the LinkedIn Pulse article MS Project: How to Round Durations Up to the Nearest Whole Day explaining how to use the function ProjDurConv with practical examples. The eBook contains detailed function description sections for all built-in functions in the Project category including ProjDurConv. This is another Pulse article, excerpted from the eBook, on using various calendars in custom field formulas: MS Project: Using Calendars in Custom Field Formulas. The pulse article MS Project: Filtering Project Data - Filter vs Formula, compiled from the eBook, and the other pulse articles on filtering explain how it works when a formula is used for filtering out the project data. Also see the article here for the VBA methods corresponding to the built-in functions.
How to Buy the eBook
This material is available to purchase only as an electronic book, that is, an eBook. There is no printed version of this eBook. The first edition released in March 2017 is no longer availeble. This is the second edition, which is 190 pages. The mpp files for the eBook are not available to purchase. The details on the electronic book format of the eBook are as follows:
Click the ordering button below if you want to purchase the eBook (Price: US$19.99). You will receive an email with the download link as soon as you complete the purchasing process.
Important Note to Purchasers
All sales are final. The purchase of this eBook is not refundable and no returns are accepted for this eBook. Therefore, review the sample content thoroughly on your computer system and make sure that both the content and the format suit your needs before attempting to buy the eBook.
All transactions will take place thru FastSpring's system (a BBB A+ accredited business in US) and the electronic book file will be directly delivered from FastSpring's servers.