Posted: April 17, 2019
Presenter: Safford Black [SB]
Moderator: Kyle [Kyle]
Please find below a transcription of the audio portion of Safford Black’s Best Practices for Making Use of Formulas in Microsoft Project webinar being provided by MPUG for the convenience of our members. You may wish to use this transcript for the purposes of self-paced learning, searching for specific information, and/or performing a quick review of webinar content. There may be exclusions, such as those steps included in product demonstrations. You may watch the live recording of this webinar at your convenience.
[SB]- Thanks everybody for joining. Again, my name is Safford Black at OnePager. We’re going to talk about how to use a formula in Microsoft Project today. This is a pretty popular topic. We get a lot of questions about it. And so we try to go into some detail; actually, if you go to our website, there’s a long running block post where people discuss [inaudible]. You’re welcome to check that out as well. We’ve tried to kind of mine the best of that as well as the best of our experiences with Microsoft Project Formula to give you a sense for really what’s important and what’s not.
Quick program note, this is a pretty in-depth discussion or at least it will be by the time we get to the end of it with lots of examples and lots of technical syntax and things like that. My recommendation would be instead of trying to furiously take notes and copy down every example, we’re happy to share the slides from this presentation afterwards and I’ll put my contact information at the end so if anybody would like a copy of the slides, I’m more than happy to send it to you.
You can just reach out to me by email. I probably won’t be able to send it to you within a five minute turnaround but, you know, try to send it to you within a couple days to make sure you have what you need. And then as Kyle mentioned, for those of you who are MPUG members, you can log in and watch the webinar in slow motion if you want to and capture those specific technical details as well.
Just a little bit about us before we get started. Most of you are probably already familiar with OnePager. If you’re not, this is what we do. We take data from Microsoft Project Schedules, whether that’s one schedule or several, whether it’s Project on the desktop or Project Server or Project Online, and we turn it into nice high level [inaudible] charts, high lines, and those types of things. There’s some examples of what we do up on the screen there. Some of those will probably look familiar to you, I’m sure.
Most of you know who we are. Anybody but—this is a refresher for those of you who don’t or who maybe haven’t done a [inaudible] in the past. Happy to talk to you about that offline as well if you reach out for slides. Obviously, that’s not the purpose of today’s conversation though.
[SB]- What we’re going to cover today is really—I’m going to walk you through, initially, an Overview of what a formula is and why it’s important. And then we’ll get into the basic structure of how to set up a formula within Microsoft Project because getting there is half the battle sometimes with the user interface in Project. So we’ll walk through that in pretty good deal. Once we’ve established how to build a formula and how not to, then we’ll get into some specific examples on the different types of syntax you can use and the different functions that Microsoft Project makes available to you. There are hundreds of examples that we could use there in terms of different syntax and functions. Obviously, we don’t have time to cover all of those today.
We’ve tried to cherry pick the ones that are most useful and in really the easiest to understand. As you guys become better versed with formula in Project, you can certainly play around with some of the other ones that are out there. Hopefully, coming at it today, you’ll kind of know which ones are the most important. Time permitting, we will get into some specific applied examples using the syntax and using the functions that we used before. And then obviously, we’ll try to have some time for questions as well.
I will make a conscious effort to pause throughout today’s webinar a few times so if there are questions, I’ve asked Kyle or rather Kyle has volunteered to facilitate those for us. I’ll pause here and there for questions if there’s some questions that are relevant to what we’re discussing and certainly cover those in stride. And if not, we’ll try to save a few minutes towards the end to make sure that we address all of those for you.
Picking the Correct Field
[SB]- Getting started with using a formula in Microsoft Project, really here are the rules of the road, right? Number one, you want to pick the correct data type and for those of you who’ve used Microsoft Project in the past, which I assume is all of you, you know that there are different types of fields in Project, right? Number fields, specs fields, date fields, these things called durations, when you’re building a Microsoft Project formula, you want to make sure that the destination of whatever formula you’re creating is of the data type you ultimately want to select.
You also want to avoid read only fields. This is not too hard to get too far down the path because the read only field isn’t going to let you put a formula in it. You’re not going to get too far with that before you realize that you can’t do it. So this is more of just a heads-up. You want to make sure that you’re using a field that will actually accept a formula. I know they’re typically going to be custom fields and I’ll walk you through the custom fields that we tend to recommend for use of formulas.
Probably most importantly, make sure that the field where you’re about to dump that formula is not already in use because whatever’s in that field prior to you putting the formula in there will summarily go away and of course, it’ll always be a field that your colleague was using and you know, that could cause a workplace strife. Before you throw a formula into a field, make sure that that field is not in use for something else.
Microsoft Project Data Types
[SB]- Specifically, when we’re looking at the types of fields that you want to use when building a formula, there are some good choices and there are some not so good choices.
Good choices, up here at the top—number fields are good and obviously those would apply to cases where you’re doing numeric calculations. Date fields would apply in cases where you are trying to calculate a date of some kind. Maybe you already have a start date and you want to add a week to that so the resulting field, when you add a week to the start date, should also be a date. So most of this is kind of common sense.
Text fields are—sometimes also called strings but Project calls them text fields—good ways to store written information, right? that aren’t numbers or dates. That’s a good fall back if you don’t know a specific type of field you want to use.
And then there are also flag fields. You’re familiar with those. Those are really what are called Boolean’s True-False. Yes-No is how Project represents them as long as you’re running Project in English. If you’re running Project in another language, you’ll see the Yes-No equivalent in your own local culture. If you need a true-false or a yes-no, the flag fields work well on that.
We tend to recommend avoiding duration fields and cost fields. The reason for that is durations are kind of this weird mishmash of a number in the front and in some kind of unit as text in the back. And that can be really hard to get formatted correctly. Even if you ultimately want to treat something as a duration, getting a formula to actually generate a duration can be more frustrating than it’s probably worth bothering with. And the same is really true of cost, right?
Costs are also a special type of number. They’re just a number that has a currency format associated with them. Again, getting a numeric calculation is relatively straightforward. Getting a numeric calculation that perfectly formats into dollars or euros or whatever—that again is typically more effort than it’s worth unless you just really, really need them to show up that way.
Common Read Only Fields
[SB]- I also said you want to try and avoid read only fields. These are some of the more common read only fields that you guys probably encounter in your day to day activities. Anyway, the critical field represents the critical path as in Yes-No or a Boolean field. The outline level tells you how far indented you are in your breakdown structure. The status field is a text field that says, “Is something late? Is something on track? Is something not started yet?” That’s an auto calculated field by Microsoft Project.
Overallocated tells you whether there are too many people allocated to the particular task. And then the WBS, everybody knows what that is right? That’s just your Work Breakdown Structure, closely related to the outline level.
Those five fields and a handful more are things that are predetermined by Microsoft Project. You don’t have any control over changing those values manually anyway and you’re not going to have any control over changing the values through a formula. Don’t even try, right? Go back to a custom field instead.
Recommended Custom Fields
[SB]- The custom fields that we recommend when you are building a formula are these ones right here. You’ll notice that they closely correspond to the types of data that we recommend. There are number fields out there, number 1 through number 20. Date fields, there are these generic dates, Dates 1 through 10. And then there’s Start 1 through 10, Finish 1 through 10.
Not mentioned here but equally valid would be Baseline Start and Baseline Finish 1 through 10—although if you’re in the habit of periodically re-baselining your project schedule, you might hesitate to use those because then you could get into a conflict between putting a formula in a baseline field and having Project put values into that field automatically because you really [inaudible] baseline.
We tend to not recommend those. Honestly, I would just probably stick with Date 1 through Date 10. Those are nice generic date fields that have a tendency not to get overwritten or step on toes from somebody else.
The text fields, there’s 30 of those. Those are very popular for a whole host of different reasons and they’re a great container to hold a formula if you’re trying to put more of a text flair on things. And then the flags, there are 20 flags out there. Yes-No. So, again, you can set logic up to automatically set that flag from Yes to No depending on what you are trying to do.
Finding Unused Fields
[SB]- To see if a field is used already—this goes back to the “Please don’t make your colleagues angry by putting a formula on top of a field that’s already being used—it’s good to look before you leap. So to check to see if a formula is already in use, go ahead and add that field to Project. And then you can put a filter on that field to see if there are already values in there.
So on the right hand side of the screen there, you’ll see a filter and this is kind of how you want things to look, right? The only thing that’s in, in this case, the Text 10 field is nothing, right? Blank. That is good. That means there’s nothing in there that you’re going to overwrite and it’s really ripe for a formula.
Now, the type of blanks are going to vary a little bit based on the type of field. Text is going to show up as blank. Flag fields default to No. There’s really not such a thing as a blank flag field. If a flag field is inserted by default, every line in there is No so you watch for, basically you want to make sure there are no Yeses in your flag field.
Date, depending on the type of date, is either going to be blank or N/A (Not Applicable) and then a number is typically going to default to 0. So just keep an eye out for those standard indicators of kind of “Nothing here to see here” and then as long as you’re seeing that and nothing beyond it, then typically it’s a safe place to put that formula.
Applying a Formula
[SB]- Once you’ve decided where you want to put your formula, now you actually need to go through the motions of putting it in there. The best way to do that in Microsoft Project is to right-click on the field header itself. In this example, I’ve inserted the field Number 1 and I’m going to right click on the word, Number 1 at the top, to get that context menu or that drop down menu that everybody’s familiar with.
And then towards the bottom, you’re going to see an option for Custom Field. Not Field Settings, that’s a different thing. That’s what you would use if you wanted to rename your field with an alias. You want to choose that second option, Custom Fields. Once you choose Custom Fields, it’s going to take you into the Custom Fields screen. Some of you guys may have seen this in the past if you’ve kind of tinkered around with Project.
Really, this field serves a number of purposes, especially if you’re somebody who uses graphical indicators—sometimes you’ll want to have a happy face when things are in good shape and a frowny face when things are in bad shape. That’s a graphical indicator. If you’re using those, you’ve probably been in here already. But it is predominantly used to create formulas.
So you come in here, right-click—it should preselect the field in question. If you right click on the field Number 1, it should preselect Number 1 there. I’ve got a little red arrow to show you that. It never hurts to double check just in case you’ve selected something kind of screwy. In general, it should preselect that for you.
Entering the Formula
[SB]- So you make sure that that is where you think it should be and then you’re going to click on the Formula button dead center on the screen. What that’s going to do is it’s going to launch the formula editor, which is—this is kind of where it gets a little bit daunting because it’s just sort of this blank cursor blinking at you and you don’t really know where to start. That’s really the meat of today’s communication in today’s webinar is to really show you once you get to that screen, what do you want to put in there.
We’ll cover all the different options for formulas in there. This is basically the formula editor though. You’ll see it’s got some buttons down at the bottom. It also has the option to insert fields and insert different functions. We’ll cover those as well. You can also copy and paste [inaudible]. So if somebody was kind enough to write a formula for you, you can actually copy and paste that formula right in here and not have to think much about it. But I would assume if you were of that mindset, you’re not going to attend this session. Kind of keep that in mind though. You can share a formula with your colleagues.
Warning: Watch those Quotes!
[SB]- One important note—if you are going to copy and paste a formula from or to a colleague, keep in mind that the quotes you see on the left “ “ are not the same as the quotes you see on the right “ “. The quotes on the left are the unformatted, unitalicized double quotes. The quotes you see on the right are the fancier open quote, close quote. They’re typically so small that I blew up the font here to like an 80 point font so you can see the distinction.
The only ones that you can use in Microsoft Project are the straight quotes on the left. You can also use a straight apostrophe. There’s an equivalent of straight apostrophes and the double quote. You cannot use the fancier open quote, close quote. That’s very important to keep in mind.
If you are typing into the formula editor and Microsoft Project is smart enough, it will not put in an open quote and close quote. However, if you are copying and pasting a formula from a colleague or maybe you found a handy dandy formula on a website somewhere like the [inaudible] website, you want to make sure that that website has not accidentally tried to make the quotes open and close because when you paste those into Project, it will not convert them and you will immediately get a syntax error when you try to run that formula. Just something to keep in mind.
I actually keep the open and close quotes in Notepad on my desktop so that as I’m typing the formula and Microsoft Word or whatever other application just really, really wants to open and close those quotes, I can go back in there and paste the correct quotation/notation as it’s needed. That’s kind of an irritant that I have with the formula editor but now that you know that it’s there, you know, keep an eye out for that syntax error. It’s clearly easy to resolve once that’s happened.
[SB]- Let’s talk about what happens to summary tasks when you create a formula. What we did before is we created a formula for this one field in Microsoft Project and, by default that formula is only going to apply to actual tasks. It’s not going to apply to summary tasks. That’s the default you see here. You’ll notice I’ve highlighted this section of the custom field window. By default, there is no calculation for a summary task. So actually, a summary task would not have this formula at all.
The third option over there, where you’ll see that radial button that says “Use Formula,” that, you kind of have to opt into that. You can choose to have the formula apply to the summary task as well but you do have to specifically select that option in order for it to work.
And then the third—well the third thing I’m talking about but the second radial button there—is this thing called Rollup. This allows you to apply a calculation. You can take the minimum of everything underneath the summary task or the maximum or the sum or the average, right? It’s all the standard statistical calculations that you would perform—you can perform that at a summary level.
If you have a calculation that’s doing a formula and that formula is doing some sort of costing, right? You’ve probably got a number field in there and each task in your project schedule is going to have a merit calculation for that cost—more likely than not, you would want the summary task above all of those [inaudible] to reflect the total cost. Probably not the maximum cost, probably not the average cost, most likely the total cost.
You would choose a roll-up and you would choose the sum function to take care of that for you. Again, this only applies to numeric fields. You cannot summarize a text field. You cannot summarize a date field. If you are doing something other than a number field, you will see that some of these options are not available to you.
[SB]- Alright. I want to take a brief minute and pause for any questions that we might have so far now that we’ve kind of covered the general layout of what a formula is and how to get there. If not, we’ll kind of move on to the next section.
[Kyle]- Safford, it looks like the queue is empty to this point. Just a reminder to everyone that you can chat over questions and Safford will answer them live during the presentation.
[SB]- Terrific. Alright. We’ll keep going. So the next thing I want to talk about are a few additional do’s and don’ts with formula and then we’ll get into the nuts and bolts of it.
Project vs. Excel
[SB]- Probably the most important thing you can take out of this webinar today—[laughs]—so if you’ve not been paying attention so far and you’re going to go take a coffee break, later. Just pay attention to this. This is the most important thing you can learn. By the way, please pay attention to the whole session. Project and Excel behave differently when it relates to formula, ok?
Everyone here has probably written a formula in Excel before. With an Excel formula, a formula in an Excel sheet can reference any other cell in that sheet or even on other sheets, right? If you had a three sheet workbook in Excel, you can have a formula that points over to another worksheet, in a different column, in a different row and it’s really, it’s kind of a free-for-all, right? That formula can reference anything, anywhere.
Microsoft Project is significantly more restrictive. The only thing that you can reference with a Microsoft Project formula is in the same row as the task in question. Each formula that you have in Microsoft Project is going to fire for every row in your schedule. Now, as we just talked about, summary tasks are kind of an exception there. But basically, for every task in your project schedule, that same formula is going to apply.
It’s akin to taking a formula in Excel and filling it down into the column for the entire spreadsheet. That’s what Microsoft Project is doing by default. You don’t have any control over that. When you put a formula into Project, it’s not just applying to one cell. It is applying to the entire column.
Because of that, Project cannot look up and down into other lines of your project plan. It can only look left and right. Every formula that you write in Microsoft Project is going to look at other fields for the task in question. It’s not going to look at what the summary task is. It’s not going to look at what the tile task is. It’s not going to look ten lines down in the schedule. It’s only going to look at that one line.
That is probably one of the least understood concepts with a Microsoft Project formula because everybody says, “Hey this is a Microsoft product. I expect that it’s going to work the same way as Excel because it’s a formula and their logos are both green, right?” It’s not. It’s really a completely different thing. That’s a very important thing to keep in mind if you intend to have your formula reference data in rows other than the current task, you will not have much success.
There are other ways you can do that: Visual Basic for Applications, DVA—that’s out of the scope of today’s webinar but that is the type of thing that you could use if you wanted to kind of go up and go down and make more advanced calculations. You can use many of the same functions with DVA.
All of the functions that we’re going to walk through today are actually just DVA functions behind the scenes, by and large. If you wanted to write a macro or you wanted to write a DVA script, you’d have a little bit more flexibility to reference other portions of your project schedule. You’d get other project schedules if you were in [inaudible] IMS or Master Project setup. But for the purposes of today where we’re really just using that formula editor, everything we’re going to do is going to be in line with the current task.
Referencing another Field
[SB]- When you want to reference another field—right?—we just talked about what can you reference and what can you not reference? You can’t reference a cell, right? A Microsoft Project formula won’t reference cell A3 like Excel does but a Microsoft Project formula will reference a column for that task. The way that you do that is you put the name of the reference field in square brackets.
I’ve got a few examples here, which I won’t read out loud to you because I trust you guys can figure out what those all look like. [Transcription Note- The examples in question from the slide presented are as follows: [Text30], [Start], [Number 10] and [% Complete]. Those were all of the examples presented.]
Basically, take the name of the field from Project, put it in square brackets and then that’s how you incorporate that into the formula. You do, by the way, want to use the system name of the fields. A lot of times, especially with text fields, people will go and they’ll say, “Well this is Text 30 behind the scenes but I’m going to call it Status or Phase or Category. Something a little bit more meaningful.” That’s great but you do want to make sure that you use the actual system name of the field when you are referencing that formula. That way, it doesn’t have trouble looking up the values it needs to.
To do that, that’s one way, right? You can just type it in if you know the names of the fields and you’re comfortable with it, you can type that in. Project can be a little bit sensitive so Text30 versus Text 30 or maybe, instead of Start, S T A R T, you spell it S T R A T, Strat, right? Typos and those types of thing. If you want to be a little bit safer, you can, in the formula editor, click on Insert Field—you’ll get a dropdown there of the fields that are available to you and they are all categorized by the field type.
In this example here, I said I want to insert a number field. I go insert field, number and it’s going to give me all of the available number fields, of which the first and probably most commonly used is percent complete. If I were to click on that there, then the formula editor would include left bracket, “percent”, space, “complete,” right bracket. That’s another way to get the field into the formula if you’re worried about typos or you can’t remember the name of the field off the top of your head.
Syntax & Functions
[SB]- Let’s get into the meat of things. Let’s talk about how to actually build some example formula using some of the more common syntax and functions that are out there. This is a lot of material. We probably won’t get through all of it in the next half hour, which is good in a way. You guys will all get credit for being here for an hour but for those of you who want to go a little bit more in-depth, there will be some functions that are in this PowerPoint slide that we probably won’t cover today unless we just happen to really power through things in the next half hour.
Again, you’re welcome to reach out to me if you want copies of these as kind of a reference guide or something. I’m happy to provide that to you offline so that you can reference things that maybe we didn’t cover live in the video today.
[SB]- Before we get into any complex [inaudible] or any complex functions, let’s start with basics. + (Plus), – (Minus), * (Star/Asterisk) and (Slash) /. Those are the most standard things that you can use. Don’t overcomplicate things. Don’t over engineer things. If you want to add two numbers together, yes there are functions that will do that but there is absolutely nothing wrong with saying Number 1 plus Number 2. That is perfectly acceptable. You can do that with string functions as well. You can [inaudible] two strings together if you want. Don’t overthink it too much.
As you’re getting started, just use those single numeric operators. It should get you most of the way there. If you find that you’re dealing with some slightly weird formatting or something, then you can get into some of these more advanced functions. But these are definitely a good place to start.
[SB]- There are a number of different categories of functions. By the way, if you go back to that formula screen, the formula editor that we had before, you’ll remember there was a dropdown that says “Insert Field.” To the right of that, there is another dropdown that says “Insert Function” and underneath that, it will give you several categories of functions and those are listed out for you right here. General, Conversion, Date/Time, Math, Text and Microsoft Project.
At a high level, general functions are going to be logical in nature. If/then, case, switch, those types. I’ll cover those for you. Conversion functions are meant, not surprisingly, to convert from one type of data to another. You might want to convert a numeric field to a text field or you might want to convert a text field to a number field with a certain number of decimal places. If you have a need to convert from one data type to another, that’s what the conversion does.
The date/time fields are going to allow you to perform calculations on dates. If you need to subtract two dates, yes you can kind of say start date minus finish date but that doesn’t always give you the best answer. There are specific date/time arithmetic functions that will allow you to take the difference between two days. That’s kind of what the date/time functions are.
Math, pretty simple right? Apart from the plus, minus, divide, multiply—most of the math functions actually, I think, are pretty worthless, apart from maybe the average or something like that. There are sign and cosign. Why would you use that in Microsoft Project? Exponent, ehh probably not. Logarithmic functions, those are basically scientific calculator things that we typically don’t see much of in the real world but they’re there. I’ll show you where those are.
Text functions, again, [inaudible] text, split in text, searching for text, within text, matching strings together, taking the first three characters of a string—those are pretty popular. And then there’s this sort of strange catch-all category which is called a Microsoft Project function.
What they really mean by that is everything above is really a [inaudible] function and can be commonly found in really any [inaudible], right? You can find them in Excel, you can find them in Access. If you’ve written [inaudible] before, those first five bullet points are going to be pretty familiar to you syntactically. Microsoft Project are [inaudible]-like functions but they are localized to Microsoft Project with a few specific features.
I like the Microsoft Project category because it gives you things that are very pointed and very specific towards project management whereas everything above it, some of you’re going to use and some of them you’re gonna not. I’d actually say if you’re getting started and getting acquainted, you might actually want to just start with the Microsoft Project functions because those are most likely to be something that you’re going to find useful. That being said, all of these Microsoft Project functions are scattered throughout these other ones as well.
The Microsoft Project functions that pertain to dates and times are listed under Microsoft Project but they are also simultaneously listed under the Date/Time category. You’re going to see some crossover. You’re going to see some repetition. That’s just really a cross reference of listing the same thing multiple places. The same function is the same function. It doesn’t matter if you’re pulling it from the Microsoft Project dropdown or from the Date/Time dropdown. It’s the same thing [inaudible]. Don’t let that confuse you.
General Function: IIf
[SB]- Probably one of the most popular general functions that’s out there is IIf, right? It’s If with two I’s. Just as a little bit of the lay of the land, all of these next slides I’m going to give you that walk you through the functions are going to be set up more or less like this. We’re going to first understand what that function is supposed to do and then give you the generic syntax of the function and then I’m going to give you an example with a screenshot.
In this case, the purpose of If and only if, and that’s what it really stands for: I-I-F is really If and only if—basically if one thing is true, then do this. If not, do that. That’s, basically you’re going to test for some expression that’s either going to be true or false. If it is true, then you do what is in the true part and if it is not true, then you do what’s in the false part.
In this example, I have created a number field: Number1 and I have created a text field, Text10. The formula lives in Text10. What I’m saying is if Number1 is greater than or equal to 0, call it positive and otherwise, call it negative. [Transcription Note- This information is based on an example provided in the slide, which is as follows: IIf ([Number1] >= 0, “Positive,” “Negative”)].
You can see here this is a really simple example, right? Negative 10 gives us the word Negative and then the next two, 0 and 10 gives us the word Positive. You can nest IIf statements by the way. If you want to say, “If it’s this, then do this and if it’s not this, then check another IIf,” you can put nested IIf statements within each other.
There are better ways to accommodate that because if you nest too many IIf statements together, it can get a little sloppy, a little hard to read. God forbid you have a problem with the syntax of your formula, it can be really, really hard to diagnose. I would say if you are going to nest your IIf statements, don’t nest more than a couple of them together. Otherwise, it’s just kind of hard to keep a handle on.
General Function: Switch
[SB]- Speaking of which, the switch statement is a better way to test several different conditions in order. That way, you don’t have to say, “If this, then this but if not, then if that and then if not, then if that.” You can just do a simple switch, same basic idea here. Instead of saying, “If this, then that,” I’m just going to say, “Switch, Number1 greater than or equal to 0, Positive. Number 1 less than or equal to 0, Negative.” [Transcription Note- The formula in the previous sentence depicted in words looks like the following when written as a formula: Switch([Number1]>=0, “Positive.” [Number1]<0, “Negative”)].
In this example, I only have two conditions because there really—there’s only a positive or a negative number. I suppose I could have put a third condition in here that said 0, right? So I could have said, “Number1 is precisely equal to 0, write the word 0,” that might be a better example. Basically, in the switch, you’re going to have an expression followed by the result, the value. Another expression followed by the result, the value. You can go on. You can have as many of these as you want. Just make sure they always come in pairs. You don’t want to end up with an odd number of parameters in this statement because that means you’re either missing an if or you’re missing a then.
General Function: Choose
[SB]- Choose is another logical function in the general category. If you’ve ever used VLOOKUP in Excel, this is kind of the same concept. Basically what it allows you to do is to look at a value in one field and then based on the order of that value, it’s going to assign a value from another field. In this case, I’m saying I want to choose based on an index in the field called Number1 and based on that, I’m going to sequence out the values from there. [Transcription Note- Here’s the example for Choose: Choose([Number1], “Alpha,” “Bravo,” “Charlie”)].
If it’s 1, it’s going to be Alpha. If it’s 2, it’s going to be Bravo. If it’s 3, it’s going to be Charlie. For those of you who do have some programming experience, you may be a little bit surprised that this index does not start at 0. It doesn’t, right? A lot of times, software systems always start counting at 0, not start counting at 1. Microsoft Project has tried to make this a little bit more user friendly and more intuitive by actually having things start at 1 instead of starting at 0, which is great except that every other application on Earth starts at 0 so anybody who has any basic familiarity with writing a formula or writing simple code, things like that, this is going to be a little bit counterintuitive.
You’re going to see this theme throughout the Microsoft Project formula engine. I’m going to mention it a couple more time. If you’re thinking about indices, remember that they start with 1 and not with 0. Other than that, it’s basically a VLOOKUP from Excel.
General Function: IsNull
[SB]- Next function is something called IsNull. Null means empty, right? Or actually, truly null, there’s a definition between null and empty that I will get into today. But it’s more widely used when we’re trying to determine if a calculation is null, not if a field is empty. You do want to make that distinction. In this case here, I’m saying—in fact, I’ve written a very simple function, 1 plus Start Date, right? [Transcription Note- The example for Null: IsNull(1 + [Start])].
That should get me—I guess the start date was May the Fourth, so now it’s May the Fifth. I’m going to say, “Is that value null or not?” It’s not when I had a date there but when Start itself is null, then the result of that calculation—I can’t add 1 to Null so it’s going to give me a Null. I do recommend against using IsNull on fields themselves. It really is better used on expressions or other calculations. It can be a little bit—I hate to use the word buggy and I see that I used it there in the presentation, I don’t really like that word, but it is, it can create some inconsistent results so it’s definitely better to use IsNull on an expression, not just on a naked field itself.
Conversion Function: CStr
[SB]- That’s the end of our general, logical functions. The next section we’re going to talk about are the conversion functions and there are dozens and dozens of these. We’re not going to cover all of them just because once you understand a couple, they’re fairly easy to interpolate what they’re supposed to do. This is one of the more common ones, CString or CStr. Basically, it converts a value to a string. It doesn’t really matter what that value is. It could be a number, it could be a date. In this case, I’m doing a CStr of percent complete. I’ve got, also, I think a CStr of a date field in there as well. [Transcription Note- The example for CStr: CStr([% Complete])].
If you look at the percent complete, 51% right here, right? And then it’s going to convert it to a text field that does strip out the formatting when it does that. You can convert from a date, that’s what we did here, so I need a CStr start date and it converts to Text30. Interestingly, I did a CStr of Duration, 1 day, and it converted it to the number 480 as text. That’s a little strange but there actually is some logic behind it. That is the number of minutes in a work day, eight hour work day. 60 minutes in an hour times 8 working hours, assuming you take an hour for lunch between 8 and 5 or maybe you’re working 9 to 5, whatever. That’s the number of minutes in a work day. You’re going to see that number 480 pop up a few more times before we’re finished here today.
Conversion Function: Val
[SB]- Value is kind of the opposite of what we just covered, or Val. It basically converts a string to a number. If you have a text field as I do here with Text10, then I’ve entered numbers in there and maybe I want to really treat those as numbers because I want to do something to them like multiply them by 2 or divide them by Pi or whatever. You can’t add numbers to text fields even if those text fields look as a number. You actually have to convert them to a number first. That’s what the Val field is designed to do. [Transcription Note- The example for Val: Val([Text10])].
You’ll see here with Text10, I’ve converted my first three pretty easily. 10 converts to 10, 10.35 converts to 10.35, 10.123 converts to 10.12 because that number field is formatted to accept two decimal places. The last one there is a bit of a trick. You can try to convert the letter A to a number. It is going to give you a 0. You do want to be mindful of that. If your text field is truly all numbers, then it’s certainly fine to convert it to a value. If you have a smattering of numbers and dates and other text in kind of a mishmash of things in that text field, just be mindful of converting it into a number because you might get some kind of strange results.
Conversion Function: CInt
[SB]- This is the last conversion function I’m going to talk about today. This one is called CInt. It converts things to an integer. You’re going to start out with a number and then convert it to an integer. There are other numeric conversion functions out there. You can see down at the bottom CBool, Boolean, CDbl, Double, CDec, Decimal, CLng, Long Integer, CSng, Single Integer. If you’re familiar with those different [inaudible] data types, you can kind of go from one to another. This is a really easy example though. [Transcription Note- The example for CInt: CInt([Number3])]
If we look again, Number3, this is our source and this is our destination. Converting 31.5 is going to round up to 32, -15 obviously is going to stay -15, converting 2.67 goes to 3 and converting 1.33 goes to 1. It’s just like rounding off a number in Excel down or up to the nearest integer. That’s going to give you that option. This is the most commonly used of the numeric conversions. The other ones out there, if you want to be more advanced, calculations are certainly available to you.
Date Function: CDate
[SB]- Date functions, these are very widely used because project management is a lot about dates, after all. The CDate function is going to convert some type of value to a date. But again, it needs to be a legitimate date. If we take a look here, I’ve got two examples for you. The first thing we’re going to do is convert Text 30 to dates. [Transcription Note- The example for Text30 and Number10 are as follows: CDate([Text30]) and CDate([Number10])].
In cases where I have typed in reasonable values into Text30, it is going to very nicely convert them into dates for me. There is no such date as “ddd” to my knowledge anyway. That’s going to give you an error as is this: April 30th 2019. To everybody on this call, you can probably decipher that pretty easily. Microsoft doesn’t like the “th” and it doesn’t like the lack of a comma. That is technically not a valid date format so if you type something in like that, you’re going to get a big fat error when you try to convert to the date format. Again, converting from text to numbers can be dicey. Converting from text to dates can also lead to problems. The nice news is errors are really easy to spot, big bold letters ERROR. You can go through there and say, “Yeah maybe this doesn’t look like a date.” Just double check those to make sure your date syntax in the text field looks reasonable.
There are also numeric dates and these are just basically counters up and down from a certain Day 0, basically, which is January 1st, of 1984. A very significant date in the history of Western Civilization, I suppose, because Microsoft has chosen to use that as their Date 0 and that is Number 30682. If you try to have a number converted to a date before that date, it will not work. If you actually try to put 0 on there, it will give you an error. You can’t go negative so you can’t go into BC or anything like that. If you’re converting numbers to dates, 30682 is the beginning date of Microsoft Project. Maybe you can go up from there, right? 40000 was sometime in 2009. 50000 is out in 2036. Again, if it’s easier for you, you can convert a date to a number, do some arithmetic on it and then convert it back to a date, right? You can switch it one way, do something to it and then switch it back the other way. Just be mindful again of the bounds that we have here just because not every number translates to a legitimate date on the Microsoft Project calendar.
Date Function: DateSerial
[SB]- DateSerial is going to help you construct a date, so this is kind of the same idea, right? Here I have Number1, Number2 and Number3. Those are my source columns and then my destination column is going into the Custom Field date 1 and I’ve gone through here and I’ve said January 4th, 2017, February 3rd, 2018—pay attention to the syntax here for those of you, well anybody really, everybody has their own preferred month-day-year, day-month-year, month-day-year—it is in this order, year-month and day.
If I want the year of 2017 [inaudible] be the year, I have to put that first. Then I have to put the month and then I have to put the day. That might be backwards for some of you. It might make perfect sense for others of you, depending on which country you’re from. Again, just pay attention to the syntax. Make sure you put the right number value and the right place to get the desired date value. Otherwise, you end up with something [inaudible] or not correct. [Transcription Note- This is the example for DateSerial: DateSerial([Number3],[Number1],[Number2])].
Date Function: Month
[SB]- There are also functions you can use—again, kind of the reverse of what we just did—to extract a portion of a date for another purpose. In this example, I’m showing you the month function which, not surprisingly, returns the month from a date and it’s going to return it as a number right? So one through 12, January through December. In this case, I’m looking at my finish date, I’m writing the month on that and I’m dropping that into a number field, so 2/13 at 2019 would be 2 for Februrary, 4/10 of 2019 is April for 4 and so on and so forth.
This one is pretty hard to mess up because it kind of assumes that you have a finish date that actually works. As long as you’re running this against a legitimate date field, you’re not going to have a lot of issues with this particular function.
There are related functions that, in the interest of time, I am not going to discuss. They are called year, day, hour, minute and second. You guys can probably all figure out what those do. The syntax is exactly the same so just depending on which date part you want to extract from a date field, you could use those to drop that into the correct destination. [Transcription Note- This is the example for Month: Month([Finish])].
Date Function: DateDiff
[SB]- Here are two fields that are a little bit confusing. We talked about adding and subtracting dates. If you’re familiar with Visual Basic for Applications, VBA, you are probably familiar with a function called DateDiff, which takes the difference between dates. That does exist in Microsoft Project but it really does a pretty lousy job of giving you, most likely, what you want to accomplish. In Microsoft Project, DateDiff is going to tell you the number of calendar hours or calendar days—not working hours, not working days but calendar—between two dates.
The reason I am emphasizing the word calendar is most Microsoft Project schedules are built based on working time, right? Unless you’re doing a 24 hour project, in all likelihood your project plan is based on a certain number of weekdays during normal working hours, 8 hours a day, etc. If you do a DateDiff between a start and finish date, let’s take a look at a one day duration.
From 1/3 to 1/3. That’s one working day. It’s giving me 540. Let’s take a look at seven days. A week, right? February 14th to February 22nd. There’s a weekend in the middle of that, if you want to check your calendar if you don’t trust me. That’s the—Valentine’s Day I think was on a Thursday of this year and the weekend was in the middle and you get to the 22nd. That works out to 201 hours. This is giving you hours in this case—or minutes, excuse me, it’s giving you minutes and I’m converting it to hours. 8.375 calendar days. You can tell right now 8.375 is not equal to 7 days.
You can immediately see that discrepancy between the calendar time and the working time and that’s why DateDiff—it’s useful but you need to know what it’s returning because if you are working off of working time, it’s not going to give you what you want. [Transcription Note- This is the example for DateDiff: DateDiff(“n”, [Start],[Finish])].
Date Function: ProjDateDiff
[SB]- On the other hand, ProjDateDiff will. ProjDateDiff is one of those special Microsoft Project functions. It’s derived from DateDiff but it’s living exclusively in the Microsoft Project world. It determines the number of working hours. Now, one downside to this versus DateDiff—DateDiff actually lets you pick the units that you want to do, I could choose minutes or days or months or whatever. ProjDateDiff, it’s just going to give me working hours.
I don’t have a way to get that into working days unless I want to divide it by 480 and that will give me working minutes basically. This is giving you—I’m sorry, it’s working minutes, not working hours. I’ll correct that before I send it out. Basically, one day is 480 working minutes. 6 times 8 is 48. Divide that by the 8 hours, 56 hours, 7 work days—and you’ll notice that when I do a ProjDateDiff, those seven work days are seven work days so that matches up with the duration.
If you are looking to calculate things based on working time, ProjDateDiff is definitely a more appropriate function compared to the standard DateDiff. [Transcription Note- This is the example for ProjDateDiff: ProjDateDiff([Start],Finish]). [Transcription Note 2- Because of the mix-up by the presenter, Mr. Safford Black, including this tip presented on the slide may help in clarification as to working minutes as opposed to working hours or working days in the terminology. Divide by 480 to get workdays (480= 8 hours x 60 minutes per hour)].
Date Function: ProjDateAdd
[SB]- Same story here, ProjDateAdd. There is a function out there that lets you add dates together called DateAdd. Don’t use it. Use ProjDateAdd, again, because we are adding working minutes to a day and that way if I add 480 to here, it goes to the next day. Here’s a good example, February 22nd which was a Friday of this year, right?
Add one work day to that, 480 minutes—bumps you out all the way to February 25th because it goes out Friday, over a weekend and in on Monday. That’s going to be smart enough to calculate how you add things to a date, being mindful of the calendar that you’ve established in Microsoft Project. There is a related function called ProjDateSubtract. You guys have probably figured out what that does. [Transcription Note- This is the example for ProjDateAdd: ProjDateAdd([Finish,[Number6])].
Math Functions: Abs
[SB]- Math functions. We talked about this at a high level. The gist of it is these are not really very useful. There’s one out here that’s called AbsoluteValue and that will basically turn negative numbers into positive numbers. That’s somewhat useful, I suppose. But everything else out there in terms of a math function, and I’ve written this up here—I’m saying, is this overkill? [Transcription Note- This is the example for Abs: Abs([Number15])].
Math Functions: Is this Overkill?
[SB]- If you’ve used a scientific calculator at some point in your life, you’re going to be familiar with them. Trigonometry function sign, cosign, tangent, exponents, logarithmic functions, square roots—those are just things that typically don’t get much use in the field of Project Management, even for a really complex earn value calculations. They’re just not things that we see happen.
But they are there, so if you ever find a need for something like that, those math functions are certainly available to you. Since this is supposed to be an Applied webinar specific to Project Management, I’m not going to cover them other than to say feel free to check it out and I think you’ll come up with a similar conclusion.
Text Function: UCase
[SB]- Last section here are the text functions. The UCase will convert everything to uppercase. In this case, I’m taking uppercase of the name field and it automatically capitalizes all of the task names there, which is nice to be consistent. There is also an LCase that does the opposite, converting everything into lower case. [Transcription Note- This is the example for UCase: UCase([Name])].
Text Function: Trim
[SB]- Trim will remove spaces and tabs. You’ll notice my task name has some lead-in spaces. It also has trail-in spaces. Those are a little hard to see, except that this field is really, really wide. The reason it’s wide is I put fifty invisible spaces after the name Task2. You can do a Trim and it will eliminate those spaces and tabs so that you only have the task name or whatever text value you’re looking for there. [Transcription Note- This is the example for Trim: Trim([Name])].
Text Function: Len
[SB]- The length function, Len, allows you to determine the length of a field numerically. Now, again, the reason I’m getting such long numbers here, Task1, actually is 45 characters long because it has a bunch of meaningless Trim spaces. That’s a good way to detect some garbage in your data.
The other thing I could have done, I could have tested this, I could have said, “Give me the length of the Trimmed name.” I could have said Len(Trim(Name)). So one function in another, that’s totally acceptable and it would have given me a more reasonable value for the task name length there. [Transcription Note- This is the example on the slide for Len: Len([Name])].
Text Function: Mid
[SB]- The Mid will allow you to grab a portion of a text field in the middle. You’ll notice here that we say, “I want to take a midpoint of a name starting at number 2 position and going for 10 characters from there.” Again, one is the left most starting position, not zero. I mentioned that a few minutes ago. That holds true for this as well. So we’re going to start on the second character and we’re going to roll out to the third character. There’s a function called Left and a function called Right as well. [Transcription Note- This is the example for Mid: Mid([Name], 2, 10)].
Text Function: StrComp
[SB]- String Compare (StrComp) allows you to decide if strings are equal to each other and if not, whether the “values” of those strings are equal or not. This is a more complex thing. I don’t see a lot of people using this but it is there if you need to make that comparison. [Transcription Note- This is the example for StrComp: StrComp([Text1],[Text2],0)].
Text Function: InStr
[SB]- InStr, this is a pretty useful one. If you’re trying to kind of do a needle in a haystack search where you want to search for one phrase within a larger piece of text, I can do that. In this case, I’m looking inside the string A, B, C. Is there an A there? Yes there is. Is there a D there? Yes there is. Is there an AB there? Yes there is.
So again, which field are you wanting to start with? What’s your haystack, what’s your needle and then it’ll—typically you would dump that into a flag field to make that determination. [Transcription Notes- The slide displays that there was no “D” when checking the string values for D. This was a quoted mistake. The example for InStr follows now: InStr(1,[Text4],[Text5])].
[SB]- A couple examples and then we’ll pause for questions. These are now the applied examples of what we’ve covered.
Example: Date Filter
[SB]- This is a good one, find all the tasks that start or finish between two dates. This is an IIf with an Or in the middle right there. If the date is before January 1st or if it is later than February 22nd, then it’s not there. Otherwise, it is. So it’s kind of a reverse but it gets you the right answer. This tells us anything that’s kind of scheduled to start or finish, basically touches the date range in question. [Transcription Note- This is the example formula on the slide: IIf([Finish]<CDate(“1/1/2019” Or [Start}>CDate(“2/28/2019”, “No”, “Yes”)].
Example: Exact Text Match
[SB]- Here’s a simple one. Double check to make sure that a text value is equal to a certain value. You’ll notice that it’s case sensitive. We’re looking for the word “Converter.” Second option there has Converter with a lower case “c.” It’s actually not picking up on that. If you wanted to make it case insensitive, you could UCase or LCase the entire thing so that it doesn’t look at the case. [Transcription Note- This is the example formula on the slide: IIf([Text1]=”Converter,”Yes”,”No”)].
Example: Fuzzy Text Match
[SB]- Here’s a fuzzy text match, another example of InStr, in string. We just covered this so I won’t cover this in a lot of detail but it will actually search for the value. In this case, it is case insensitive so there is a little bit of an inconsistency there as well. [Transcription Note- This is the example formula on the slide: IIf(InStr(1,[Text1],”Converter”),”Yes”,”No”)].
Example: Baseline Slippage
[SB]- Baseline slippage, we can do a ProjDateDiff between the baseline and today’s date. There’s another little function out there called CDateNow that gives you today’s date as a date. Divide by 480 to get the number of working days and assuming our baseline, I think was probably today, that’s 75 days from January 1st. [Transcription Note- This is the example formula on the slide: ProjDateDiff(CDate([Baseline Start]),CDate(Now())) / 480].
[SB]- Alright. Got a couple minutes for questions. I’m happy to answer anything that comes up.
[Kyle]- Safford, that was great. Thank you so much. We do have a couple questions that have come in. The first one here is from Neil. He’s asking about the other to handle nested IIf statements was. I’m not sure if you mentioned there was two ways.
[SB]- Let’s review it. Yeah. So that Switch, and in fact when you get the slides, I showed the nested IIf statements and then the immediately following slide was the Switch where it just says, basically, “Expression, result, expression, result, expression, result.” It is essentially a nested IIf statement but without all the expert parentheses.
[Kyle]- Ok, gotcha. Great. Another question here is from Sarah. Just looking to clarify on VLOOKUP, if it’s limited to common project data only or if it’s able to pull data from another source.
[SB]- It would be pulling data from Microsoft Project so it wouldn’t pull it from, you know, Excel Spreadsheet or something like that. The VLOOKUP comparison was really made to, sort of, for people who are familiar with that concept in Excel but it is going to do that based on only the data in Microsoft Project.
[Kyle]- Ok great. Thanks for clarifying. Looks like that is it for questions here. Just to point it out, Safford has his contact information on the screen and he is willing to share those slides with you. You just have to reach out to him via email there. If you take a screenshot, you can click the icon at the top of the viewer window, the screenshot icon and that will just save it right to your desktop for referencing later. Yeah, so with that said, it takes us pretty close to the end here. Safford, anything else before we close out today?
[SB]- Yeah. I appreciate all the great questions. Hopefully this is useful and like I said, feel free to reach out if you want a copy of the slides. As I think I said, I probably won’t get those turned around in five minutes but certainly by, if you request them today or this week, try to get them to you by Monday so they are kind of [inaudible].