Hacky Ways To Comment MS Project Functions & Some Excel Too

How I add comments to complex MS Project  formulas on fields -

Quick answers:

• MS Project:
• & IIf(False, "Insert your comment here","") - for custom text fields
• & IIf(True, "", "Insert your comment here") - for custom text fields
• Cint(), Int(), etc. return #ERROR on string inputs so they are not useful
• Excel:
• + N("Insert your comment here") - use in Excel cells that are to return numbers
• & If(False, "Insert your comment here","") - for text cells
• & If(True,"", "Insert your comment here") - for text cells
• OR ... use the standard comment on the cell features (right click, insert comment)

Generally if you can concatenate a function to a string that returns either "" or nothing and you can hide your comment in a parameter to that function you are good to go. For numbers, add functions like N() that will return a zero value added at the end of an otherwise valid formula. This assumes the function returns zero when the text of the comment is a parameter to the function ... make sense?

This is written mainly for MS Project. The approach is applicable to Excel. The formulas look very different but a lot of the functions and syntax are close or the same.

If you Google "commenting Excel functions" or something like that you'll get back a butt load of results. This is nothing new. All I'm doing here is trying to make the answers more general and explain what's going on. Improvements & corrections are more than welcome of course.

Bigger examples:

IIf([% Complete] = 100 AND CDate(ProjDateConv([Actual Finish], pjdate_mm_dd_yy)) <=
     CDate(ProjDateConv([Status Date], pjdate_mm_dd_yy)), "Done",
          IIf((ProjDateValue([Baseline Finish]) = ProjDateValue("NA")),"No Baseline",
                    "Not Done / Update"))

This is part of a 40 + line nested IIf wad of chaos, frankly. So here's what that might look like using the commenting tactics we are discussing here.

IIf([% Complete] = 100 AND CDate(ProjDateConv([Actual Finish], pjdate_mm_dd_yy)) <=
     CDate(ProjDateConv([Status Date], pjdate_mm_dd_yy)), "Done"
                    & IIf(False, "check for 100% and for end dates in the future",""),
          IIf((ProjDateValue([Baseline Finish]) = ProjDateValue("NA")),"No Baseline",
                      "Not Done / Update" & IIf(False, "check for baseline","")))

The comment bits are in bold.

Both those work in MS Project 2013. You will need to set a project status date or it will error out. A baseline is needed to get any result back except "No Baseline"for an incomplete task. The full formula deals with these conditions.*

For Excel:
Excel uses If() statements not IIf(). The form of the parameters is the same in both IE:

Project - IIf(condition, result if true, result if false) - the result if false is optional
Excel - IF(condition, result if true, result if false)

A couple of short Excel Examples:

=(99+100) & IF(FALSE, "this is a comment","")

=(99+100) + N( "this is a comment")

The first one will make the cell a text / string value. It will not have decimal places unless you put them there in a more complex string conversion. Sometimes that's what you want.

I would use the N() technique with dates

General Idea:
Still scratching your head? Think I'm nuts? I am nuts, but, let me try to clear up how this works and why we'd want to use it.

The IIf() and If() functions return something based on a condition being true or false. All we are doing is forcing a true or false by putting the key words "True" or "False" right in the condition. If we substitute "True" with "1=1" or "False" with "1=0" it would do the same thing. (omit the quotes in the actual formula),

For " & IIf(False, result if true, result if false)" form of this, the "result if false" is replaced with double quotes ("") . Double quotes is the same as no character. So this forces the IIf() to return no character.

The ampersand ( "&" ) means "concatenate" or "stick one text string to another text string". So, it then sticks no character to the previous text string. The result is that you get a comment in your formula & the output is the same as it would have been.

The + N("some comment") is similar. N("string") returns the number* zero (0).  The plus ("+") just say to add our zero to the number that is returned by the leading part of the formula. A number + zero is the same number! See you can count on some things being true ...

Note that in Excel speak "number" refers to number that can be different kinds of numbers like integers or floating point. So don't count on it being a particular type. In this case, zero is zero, so no worries. Here's some more on N()

Why do this?:
If I had a simple one to ten line formula, I would not do this. It's probably not useful. Or, in Excel, the standard comment on the cell is going to be more useful.

When I have something longer than ten lines and it's not working right, I often need to put reminders in the code about what a chunk of a formula is doing. It helps see where I may have put something out of sequence and sometimes it helps me see where I may have missed closing parentheses, commas, etc.

That said, this is a hack. We are doing things with function they were not ever intended to do. It also does not help anyone else but you read these giant formulas more easily. You should consider stripping them out once you debugging is complete and the formula is stable, in my humble opinion.

Issues & Gotchas:
"False Part Not Optional" - With MS Project 2013 desktop client the "false part" for the IIF() is optional. BUT, on Project Server / Project Online (in the cloud) it is not. I have not tested it with and on premise installation of Project Server. I would just make sure there is a "false part")

Non-Printable chars at the end - FYI - The IIf() & If() forms of these may add a non-printable o the end of the string they are concatenated to. Excel has the CLEAN() function for this. You may need to resort to other string handling functions if this is a problem. Seems to be okay in Excel. When I C&P to a text editor I seem to get a line break.

*I do not generally post or otherwise publish full formulas or programs. Sorry. Contact me if you have any questions beyond that.