Mastering Custom Field Formulas in MS Project | Articles


By Ismet Kocaman

 

 

 

How to Calculate the Date of a Particular Day in a Month

 

 

Version 1, Published on January 6th, 2016

Please first see Disclaimer, Copyright Notes and Trademark Notes sections on the bottom of this page. This document is related

to all standalone desktop versions of MS Project Professional/Standard supporting the custom field formulas feature.

 

Suppose that there will be a meeting (or a report will be issued) on the last Wednesday of each month during the project. And also suppose that you do not need to assign resources to this meeting that takes place monthly. Then you could simply create a recurring task representing the whole meeting schedule in some other place in the project plan’s task list or you may even keep a schedule of the meeting outside MS Project (for example, in an Excel sheet) for planning and tracking the occurrences of this event. But, this time, let us say, you also want to locate the tasks taking place on the week of this meeting along with a day counter to the meeting based on the current date. How can we achieve this ?

 

We can use some custom field formulas for this purpose. So in this article, we will discuss how to develop formulas calculating date of a particular day in a month, which is the last Wednesday of the current month in our scenario. 

 

Let us start with creating a formula to calculate the date of the last Wednesday on the month specified by any date; the steps are as follows:

 

  • First, find the number of days between the last Wednesday and the last day of the specified month (excluding Wednesday) by using the WeekDay function as explained below (see the related section on the book for a complete definition of the WeekDay function):

 

  • Use 4 for the parameter firstdayofweek; that is, make Wednesday the first day of the week (i.e., pjWednesday).

  • Use the date of the last day of the specified month for the first parameter date of Weekday.

  • Subtract 1 from the returned value.

This is the resulting formula:

WeekDay( date_of_last_day_of_specified_month,  pjWednesday ) - 1

  

  • Now subtracting the number of days calculated above from the date of the last day of the specified month yields the date of the last Wednesday in the specified month. See the resulting formula below:

 

DateSerial( Year( [Date1] ), Month( [Date1] )+1, 0) –

(Weekday( DateSerial(Year( [Date1] ), Month( [Date1] )+1, 0), pjWednesday )  -  1)

 

Now the question to ask would probably be "Why not calculate the 4th Wednesday of the specified month instead of the last Wednesday ?".  It is because the number of Wednesdays in some months may be 5 instead of 4, for example, the months March, June, August and November in 2016. As a result, calculating backwards from the end of the month simplifies the formula and also guarantees to get always the last Wednesday regardless of whether it is the 4th or the 5th one. The formula can be modified for the other days of the week. If we substitute [Current Date] for [Date1] in the formula above, then the formula returns the date of the last Wednesday of the current month: 

 

Date10 field’s formula calculates the meeting day on the month of MS Project’s current date:

 

DateSerial( Year( [Current Date] ), Month( [Current Date] )+1, 0) –

(Weekday( DateSerial(Year( [Current Date] ), Month( [Current Date] )+1, 0), pjWednesday )  -  1)

 

We now have the formula that we need, and we can use it to implement our scenario described in the first paragraph. As mentioned above, you also want to locate the tasks taking place on the week of this meeting along with a day counter to the meeting based on the current date. For this purpose, the formula below can be used:

Text10 field’s formula:

 

iif(  ( [Date10] Between DateValue([Start]) And DateValue([Finish]) ) And

DateValue( [Current Date] ) <= [Date10],

ChrW(9658) & ProjDateConv( [Date10], pjDate_ddd_mmm_dd ) & " (" &

[Date10] - DateValue( [Current Date] ) & " days)","")

 

The formula above works as follows: if the meeting day falls into the range of dates between task’s start and finish (inclusive), and also if the current date is less than or equal to the meeting date, then Text10 field’s formula returns the meeting date along with the number of calendar days left to the meeting, starting from the beginning of the month. It is possible to mark the meeting date on the task bars, and the related settings are explained below:

 

 

 

MEETING DATE entry in the Bar Styles dialog box:

 

Show For … Tasks: Normal, Flag20, Not Manually Scheduled

From: Start10, To: Start10

 

The Start10 field’s formula is [Date10], and the Flag20 field’s formula is [Text10] <> “”.

 

See the final schedule below: 

 

Note that the Date1 field shows the current date with the formula [Current Date] for information. Both the Date1 and Date10 columns can be hidden after completing testing the formula.

 

Note    The ChrW function is not listed in the Function button but it is recognized in formulas. It returns text (i.e., a String) containing a Unicode character, if the system supports Unicode which is International Standards Organization (ISO) character standard. Unicode set includes symbols such as geometric shapes. For example, black right-pointing pointer symbol (>) is a Unicode character and it is represented by the hexadecimal code 25BA (use the Symbol dialog box in Office Word or Excel to see the list of Unicode characters). This character is supported by many fonts. Therefore, ChrW("&H25BA") returns a black right-pointing pointer symbol (or character) to a custom text field. Format("&H25BA") gives the decimal code for the same character, which is 9658, so the formula ChrW(9658) also produces the same output. Note that AscW([Text10]) returns the Unicode character code 9658 (the function gets the first character in the field); and Hex(AscW([Text10])) returns the hexadecimal code 25BA to a custom text field.

 

 

How to calculate the date of the third Wednesday in a month specified by any date

 

Let us now develop a formula to calculate the date of third Wednesday on the month specified by any date. Follow the steps below:

 

  • First, find the number of days between the first Wednesday and the first day of the specified month (excluding Wednesday) by using the WeekDay function as shown below:

 

  • Enter 4 as firstdayofweek (i.e., make Wednesday the first day of the week).

  • Enter the date of the first day of the current month as the first parameter.

  • Subtract 1 from the returned value.

 

    This is the resulting formula:

WeekDay( date_of_first_day_of_specified_month,  pjWednesday ) - 1

 

  • Now subtracting the number of days calculated above from the date of the 22nd day of the specified month yields the date of the third Wednesday in that month. The date of the 22nd day is the number of days in 3 full weeks plus the date of the first day:

 

DateSerial(Year( [Date1] ), Month( [Date1] ), 1) + 3*7 - 

(Weekday(DateSerial(Year( [Date1] ), Month( [Date1] ), 1), 

pjWednesday )  -  1)

 

The formula can be modified for the other days of the week.

 

 

How To Calculate Date of the Next Particular Weekday Based on any Date

 

 

Formula to calculate the date of the next (or previous) Tuesday if the current date is not a Tuesday

 

Suppose that you need a formula to calculate the date of the next particular weekday based on any date specified. For example, the following formula returns the date of the next Tuesday if the current date is not a Tuesday:

 

iif( Weekday( [Current Date], pjTuesday ) <> 1,

1 + [Current Date] + 7 - Weekday( [Current Date], pjTuesday ), [Current Date] )

 

Note that the other enumeration constants can be substituted for pjTuesday in the formula; for example, the formula will always return Fridays when pjFriday is used in place of pjTuesday. Also any date, for example, [Date1] can be substituted for [Current Date] in the formula.

 

And the following formula returns the date of the previous Tuesday if the current date is not a Tuesday:

 

iif( Weekday( [Current Date], pjTuesday ) <> 1,

1 + [Current Date] - Weekday( [Current Date], pjTuesday ), [Current Date] )

 

In order to test the formulas above, let us use the Rnd function generating random numbers to produce some random dates. The formula  Int( (Max – Min + 1) * Rnd + Min) produces random integers in the range from Min to Max. As a result, the formula Int((12-1+1)*Rnd+1) returns random integer month numbers, while the formula Int((31-1+1)*Rnd+1) returns random integer day numbers (don't worry, MS Project automatically adds parentheses to Rnd). The example below demonstrates how they work:

 

Date1’s formula:

 

DateSerial( 2017, Int((12-1+1)*Rnd+1), Int((31-1+1)*Rnd+1) )

 

Date2 and Date3 contain formulas returning the previous and next Tuesday’s date; in these formulas, all [Current Date]s are replaced with [Date1]. Clicking <F9> updates the dates in Date1’s column.

 

 

 The formula Int( (Max – Min + 1) * Rnd + Min) can be customized to produce any random value in a range specified; for example, the following formula returns random dates between 1st anf 15th of February (inclusive).

 

Int( (#February 15, 2017# - #February 1, 2017# + 1) * Rnd + #February 1, 2017#)

 

The following example demonstrates how to use these formulas. The scenario is as follows: suppose that you want to see the quarters which the task start dates fall into. And also the quarters start on Saturdays in your project environment. Therefore, the calendar quarter information related to the task start dates must be adjusted based on this requirement. In this example, we will modify and use some formulas developed previously in the Date2 and Date3 fields:

 

  • The formula below returns the date of the first day of the calendar quarter in which task’s start date falls (or of any other valid date passed):

 

Date2 field’s formula (renamed as SOQ, that is, the Start date Of the Quarter):

 

DateSerial( Year([Start]), Int((Month([Start]) - 1) / 3) * 3 + 1, 1 )

 

  • Next, the formula below returns the first Saturday of that quarter:

 

Date3 field’s formula (adjusted start of the quarter):

 

iif( Weekday( [SOQ], pjSaturday ) <> 1, [SOQ] + 8 - Weekday( [SOQ], pjSaturday ), [SOQ])

 

By using the two formulas above, a new formula can be developed to obtain the quarter number in which task’s start date falls, calculated by starting the quarter on the first Saturday:

 

Text1 field’s formula (formula to calculate the start of a quarter on a particular weekday):

         “Q” & iif( DateValue([Start]) >= [Date3],

DatePart("q",[Start]) & “ “ & Year([Start]),

DatePart("q",DateAdd("q",-1,[Start]) ) & “ “ & Year(DateAdd("q",-1,[Start]))

)

Text1’s formula checks whether the task starts on or later than the first Saturday of the quarter. If so, this means that the task’s start falls in the current quarter, then true part is returned. Otherwise, the formula returns the previous quarters information by the false part. Text2 shows the calendar quarters calculated by the formula,

 

"Q" & DatePart("q",[Start]) & " " & Year([Start])

 

The picture below shows the results of the formulas:

 

 

Note that the quarter information displayed in the Text1 field for T2 does not match the one shown on MS Project’s timescale, as we would expect; Text1 shows that T2 falls in the previous quarter instead of Q1.

 

 

Calculating the number of the week in which a task starts relative to the start of a quarter

 

The Text5 field’s formula below calculates the quarter of the calendar year in which a task’s start date falls, as well as the number of the week in which a task’s start falls, starting from the beginning of the quarter:

 

Text5’s formula (Project’s setting for Week starts on is Monday):

 

"Q"  & DatePart("q",[Start]) &

"W" & DateDiff("ww", [SOQ], [Start],  pjMonday ) + 1

  

Date2 field’s formula calculates the start of the calendar quarter in which Start’s date falls (renamed as SOQ, that is, the Start date Of the Quarter):

 

DateSerial( Year([Start]), Int((Month([Start]) - 1) / 3) * 3 + 1, 1 )

 

Text5’s formula works as follows:

 

  • DatePart returns the calendar quarter of the year in which a task’s start date falls.

  • DateDiff’s last parameter is pjMonday, therefore, it counts the Mondays between the beginning of the quarter (exclusive, if a quarter starts on Monday) and the task’s start (inclusive, if task starts on Monday). 

     

    Suppose that the task does not start on the first week of the quarter. The DateDiff counts Mondays but it does not count the first week even when the calendar quarter starts on Monday of the first week. Therefore, we need to add 1 to the output of the function. This adjustment will also help with the case where the task starts on the first week of the quarter, so the result will be 1 instead of 0 as it is seen below:   

 

     

    In the example above, the project begins on the start of the 1st quarter, and the Middle tier is set to display “Weeks” in the format “W1, W2, W3, W4, ...(From Start)” on the timescale. Thus, the week number shown in the Text5 field can be compared to the week number displayed on the timescale.

 

    On the other hand, Text5’s week number will not match the one on the timescale, but it will count the weeks correctly, when the project starts on a weekday which is Wednesday or later; see in the example below:

     

     

    Also in this example, project starts at the beginning of the quarter, but the timescale shows the quarter on W-1.

     

    The Text5 field can be used to group the tasks. And Start in the formulas can be replaced with any other date type field reference.

 

 


Revision History:

Version 1 - January 6th, 2016 - Initial document


 

 

 

Disclaimer

The information contained in this document is intended only for the general interest of its readers and should not be used as a basis for making any business or other important decisions. Though all the efforts have been made to create accurate content, mistakes can occur. The author of this document cannot, therefore, guarantee the accuracy of content. The author of this document disclaims all warranties and must advise you to use this document at your own risk. The author of this document is not liable for loss of any nature resulting from the use of or reliance upon the information found therein.

 

Copyright Notes

All Content Copyright © Ismet Kocaman | ikocaman.pm[at]gmail.com | No part of this document may be reproduced, stored in a retrieval system or transmitted in any form or by any means, without the prior written permission of the Author (Ismet Kocaman). Do not quote/refer to the parts of the document and do not use it as training material without permission. 

 

Trademark Notes

Microsoft® is a registered trademark or trademark of Microsoft Corporation in the United States and/or other countries. The author of this document has no affiliation with Microsoft Corporation. All other trademarks mentioned herein are the property of their respective owners. Screen captures were used with authorization from Microsoft Corporation. This document is not a product of Microsoft Corporation.