Anyone who has used OnePager Pro knows that flag columns (e.g. “Flag20”) are an easy way to decide which tasks and milestones you want to include in your project report. But, what if you have a lot of tasks, and you don’t want to hand-select the important ones?
Enter the Microsoft Project formula. Instead of changing flag fields by hand, you can have a formula do it for you based on the values in your project plan.
Just like formulas in Excel, MS Project formulas can look at the data in your project plan and dynamically adjust the value of any flag, text or number column. This gives you a lot of flexibility to set up import rules, apply them to a flag field, and then have OnePager Pro automatically import the tasks and milestones that meet your criteria.
To add a formula to a flag field, just follow these steps:
1. Right-click on the flag field you want to customize and choose Custom Fields.
2. Under the Custom Attributes section, click the Formula button.
3. A formula editor will appear, allowing you to insert different fields and conditions. We’ll show you a few examples later in the post.
4. After you’ve written your formula, you will have the option to apply it to summary tasks as well. This is highly recommended, especially if you are creating an executive summary report. Our recommended setting for summary tasks is the Rollup option, with the dropdown set to “OR”. This means that if any child tasks have their flag set to yes, the summary task will be brought in as well.
5. Click OK again, and the formula will be applied to all of the tasks and summary tasks (if you chose to roll-up) in your project plan.
As the values in your project plan change, the formula will automatically update the flag column. So, for example, if you want to create a OnePager Pro chart for all tasks assigned to Randy, and you change a task’s assignment from Randy to Jack, the formula will de-flag the task, and the task will disappear from OnePager Pro when you do an update. Pretty neat, right?
Example Microsoft Project Formulas
So, what kind of formulas can you create in Microsoft Project? The sky is the limit, and you don’t have to be a developer or a propeller-head to write one. Here are some examples:
Formula: IIf([Text1]="Converter","Yes","No")
Meaning: Set the flag to yes for any tasks where the Text1 field is set to “Converter”. This is a great way to filter tasks based on a pre-determined category.
Formula: IIf([Summary]=True,IIf([Outline Level]=2,"Yes","No"),"No")
Meaning: Set the flag to yes for any summary-level tasks at outline level #2. If you want to show an executive-level summary of your project without hand-selecting summary tasks, this is a good way to quickly grab a specific level of detail.
Formula: IIf([Start]>=CDate("9/14/2013") AND [Start]<=CDate("9/28/2013"),"Yes","No")
Meaning: Set the flag to yes for any tasks that start between 9/14/2013 and 9/28/2013. This formula is good for identifying tasks that are starting during a given period of time, and can help you focus your team on what to do next.
As you can see, there are limitless possibilities when it comes to writing these formulas. Need to identify tasks that are late, over-budget, or at risk? Let your PM tools do the heavy lifting for you. The formulas in MS Project will find the tasks for you, and OnePager Pro will present them beautifully.
Need a hand writing a custom formula in MS Project for a specific purpose? Just post it here, and we’ll try to respond with some options!
I have a master file with numerous projects I have created a “stoplight” graphical indicator column and a flag column. I would like to roll up only certain tasks to show their status in the project summary, therefore each project, closed in the master will still show the stoplight status. With the flag column I can change to different tasks by clicking yes, and they will rollup as well.
Sorry. My question is what formula do I need to get the flag column to rollup the status indicator to the project summary level? Thank you.
You’ve hit on an annoying inconsistency in Microsoft Project. Custom calculations (formulas, stoplight columns, etc.) do not roll up to the project summary task in an integrated master schedule. This is true even if you have told the formula to do so for summary tasks. The formula will apply to summary tasks, but not to the project summary task.
The workaround we tell our customers is to reapply the formula in the IMS *in addition to* the individual project plans. Technically, this means you have a formula or calculation in two places, but it will get around the MS Project inconsistency.
I would like to have the stoplight be also shown in the summary. i already have the formula so that numbers/values will be shown even in the summary. however, when I incorporated the graphical indicator, the stoplight on the summary disappears. I also checked the “roll-up” something. Do you have other way to do this?
I think I found out how to put the stoplight on summary status:
on the Calculation for task and group summary rows in the custom fields in Format tab, choose Use formula (which is used in custom attributes)
Choose also graphical indicators (to determine the colour of your stoplight)
identify the values under your test. click ok. once you have seen the values, return to graphical indicators.
on that window, choose summary rows and check summary rows inherit criteria from non-summary rows. click Ok.
It worked on my file.
I need to write a formula for a stoplight flag that indicates whether a task has been split or not. I cannot figure out how to do this. Can anyone help?
A coworker is setting up an MS Project…project, and she asked for some help adding a custom field that would convert the Start date field into our Fiscal Quarter Fiscal Week schema, to display as FQFW, e.g. Q1W1. I’ve written the formula in excel, but I’ve never even touched Project until this week. Apparently it doesn’t take formulas quite the same way as Excel does. I tried using the ”Switch” function in Project, and it worked, but it only accepts 14 arguments, and there are obviously 52 weeks we’re dealing with. Does anyone have any suggestions? Would there be a way to do this using VBA? (I know next to nothing here as well.)
Formula where A1 is the Start Date:
Code:
=IF(AND(A1>=DATE(2014,2,1),A1=DATE(2014,5,3),A1=DATE(2014,8,2),A1=DATE(2014,11,1),A1<=DATE(2015,1,30)),CONCATENATE("Q4","W",(INT((A1-DATE(2014,11,1))/7)+1)),FALSE))))
Any and all suggestions welcome.
Thanks.
This would end up being a pretty nasty formula in Microsoft Project and would probably only work some of the time–especially when you consider that year/quarter boundaries don’t always align with weeks, meaning sometimes there is a 52nd week and sometimes there is a 53rd. Unfortunately, we find that week-based computations are some of the most unstable things you can attempt due to all of the exceptions.
You might want to have your colleague download a demo license of OnePager Pro. It is our reporting app for Microsoft Project and can be configured to display a custom fiscal quarter and fiscal week on the time axis and/or next to the task itself. All you have to do is import your MS Project plan into OnePager Pro and tell it how you want to format the time axis. From there, a simple copy/paste into PowerPoint will give you a visual representation of the project plan without having to do too much hacking around with Project formulas!
Hi Safford,
I am trying to configure MS Project 2010 to provide preset duration values for a set of tasks (e.g Define, Develop, Test, … ) depending on a customised field named Complexity with values Low, Medium, High, where the values are different for each task. Can you suggest a suitable approach?
Thanks,
Syd
Syd,
You can do this with a formula on the Duration column that looks at your task type and the custom field that you are using to identify complexity. I would structure this as a series of nested “IFF” statements that cover all of the permutations of task type and complexity.
I have a template for new product design with four different phases as summary tasks. I would like to create a custom column that populates the current phase. I figured it would work by looking at the percent complete of each phase and if one is less than 100% then that would be the current phase. How would I go about doing such a thing. Thank you.
The following formula will insert the text “Current Phase” into a custom text field if the phase is not complete, but has at least started.
IIf([% Complete]<100 And [% Complete]>0,"Current Phase","")If you want to get fancier, you could use percent complete to determine which phases are past and future as well, using additional conditions:
IIf([% Complete]<100 And [% Complete]>0,"Current Phase",
IIf([% Complete]=100,"Past Phase",
IIf([% Complete]=0,"Future Phase","")))
Once you have tagged phases as past, present, and future, you can import this text into OnePager Pro and assign color to the different phase status so they stand out even better.
Thank you for the quick reply. I understand how your formulas will work, but my template is setup in a way that the formulas will not work. I have the main summary task names as the phase names (ie. Phase 0, Phase 1, etc.). Let’s say I’m 100% complete with Phase 0 and Phase 1 is 50%, meaning I am on Phase 1, how could we write the formula to consider only the main summary tasks percent complete, and use the less the 100% complete task’s name?
Unfortunately, Microsoft Project does not let you reference another line of your project plan in a formula, even if it’s a parent task. Project formulas are much more limited than Excel formulas, where you have the ability to point to a completely different part of your spreadsheet.
I’m struggling to determine/auto-calculate “planned % complete” within project.
Summary:
What I am doing works for individual tasks, but the logic applied automatically by project when using the “rollup” radio button function is not correct.
Details:
I’m first calculating days of duration for each task. Formula looks like:
Val(ProjDurConv(Duration,pjDays))
Then I’m calculating elapsed days for each task based on current date and planned start date. Formula looks like:
IIf(ProjDateDiff([Start],[Status Date])/480>=Val(ProjDurConv([Duration],pjDays)),Val(ProjDurConv([Duration],pjDays)),IIf(DateDiff(“d”,[Start],[Status Date])<=0,0,ProjDateDiff([Start],[Status Date])/480))
Then, I'm dividing elapsed days by days of total duration to determine planned % complete. This works great for individual tasks, and is maybe even working for rollup summaries if there is no task overlap, but if there is any overlap in tasks, the rollup is definitely not accurate (it is not taking into account any weighting based on actual durations as it should/does with rollup of normal % complete.
I've thought about using a formula to determine if the task is a summary task (i.e. has substasks), and if so, to use a more mathematical function of multiplying individual planned % complete by duration and then summing, and then doing the simple divide. Would love any suggestions for how to do this or approach a different way to accomplish ACCURATE planned % complete at a summary level.
I think what I really need is to be able to reproduce the logic that project uses when doing the rollup of normal % complete at summary levels based on user input, but instead based on calculated values.
Thanks in advance!
Summation of values into a summary task goes beyond the scope of Microsoft Project formulas. Microsoft Project does not allow a formula to reference other rows of the schedule–only other columns within the same row.
In your particular case, the calculation and display of summary percent complete is something our users have struggled with for some time. We eventually built a custom calculation in OnePager Pro (not in Project itself) to look at the percent complete values of all summary tasks and integrate them (think calculus) over the total time scheduled and the total time elapsed. The resulting calculation displays a percent complete value that is more accurate because it considers the progress AND the schedule of the underlying child tasks.
I require the Planned % complete at status date to be reflected in a separate field. ie What should the Baseline % complete be at status date of the project. This will be an easy “view” on how far behind you are on the project. It will also assist with filtering the tasks that are continuously slipping.
Your input will be appreciated.
Regards
Andre
I would recommend looking at the duration between the current date and your baseline start date. Comparing that duration to the original baseline duration will give you a sense for how far along you should be.
You can use the “Baseline Duration” field in Microsoft Project to do this, or you can subtract the baseline dates, depending on whether you want to consider weekends in your calculation. I would avoid calculating anything based on “Start Date” or “Finish Date”, as those fields are subject to change as your project is updated. Baselines, on the other hand, are supposed to stay the same, and will give you a better sense of your original plan.
This is a complex formula, so it’s probably easier to set it up in smaller pieces like this:
Number1: Number of days between your original baseline start date and today:
DateDiff("d",CDate([Baseline Start]),CDate(Now()))Number2: Number of days between your original baseline start and finish dates (i.e. your original planned duration):
DateDiff("d",CDate([Baseline Start]),CDate([Baseline Finish]))Now, we can compare these two intermediate values to determine what the planned percent complete should be:
Number3: Percentage of days elapsed from planned start compared to planned duration:
Iif ([Number1] <= 0, 0,Iif ([Number1] >= [Number2], 100,
Iif ([Number2] = 0, 100,
Iif (([Number1] < [Number2]) AND ([Number2] > 0), [Number1]/[Number2]*100, 0))))
The meat of the calculation is in the fourth condition. The first three conditions account for tasks haven’t started yet, or should have already finished, including zero-duration milestones.
If you don’t want to waste two extra number columns with intermediate values, you can combine everything into a single formula, though it’s quite a bit harder to read:
Iif (DateDiff("d",CDate([Baseline Start]),CDate(Now())) <= 0, 0,Iif (DateDiff("d",CDate([Baseline Start]),CDate(Now())) >= DateDiff("d",CDate([Baseline Start]),CDate([Baseline Finish])), 100,
Iif ((DateDiff("d",CDate([Baseline Start]),CDate([Baseline Finish])) = 0), 100,
Iif ((DateDiff("d",CDate([Baseline Start]),CDate(Now()))) AND (DateDiff("d",CDate([Baseline Start]),CDate([Baseline Finish])) > 0), DateDiff("d",CDate([Baseline Start]),CDate(Now()))/DateDiff("d",CDate([Baseline Start]),CDate([Baseline Finish]))*100, 0))))
Safford
Thank you this is excellent, Is there a way to sum the total into the Summary Tasks?
Appreciate the input.
Regards
Andre
Project does let you calculate the sum, average, and maximum of child tasks at the summary level. See the “Rollup” options in the first screenshot at the very top of the original blog post.
However, based on your requirements, I don’t think that any of these calculations are going to give you what you want. Instead, I would recommend choosing the “Use Formula” option for summary tasks and re-hashing the exact same formula that you used for child tasks. This will calculate the percent complete value cleanly and consistently for the summary tasks.
Stafford, thank you, yes it does work.
I do have one last issue to resolve, earlier I indicated that the Roll-up to the Summary Task or Project Summary “works” It rolls-up but the values are incorrect. I read through the other posts but cant make headway of it. You mention that I must “rehash the exact same formula used for the child tasks for the summary tasks” sorry but I don’t follow ? I did select “Use Formula” but I am unsure of what other settings or steps I require to execute.. Please can you assist with clarity. Thanks
Selecting the “Use Formula” radio button is all you need to do. This tells Project to copy your original formula and apply it verbatim to summary tasks.
The calculated values are going to be based on the baseline information at the summary level, which is inherently flawed, meaning that the calculated values at the summary level will be close, but not completely accurate.
For example, if you have a milestone on August 1st and a milestone on September 1st, you would probably say that the correct value would be 50% complete since we are at August 20th on the calendar. However, the summary task is actually a task (not a milestone) with a duration of 31 days. Twenty days into that, you get a projected percent complete value of (20/31) or 64.5%.
Unfortunately, Microsoft Project formulas do not give us a way to “look underneath” a summary task at the types of tasks that roll up to it, meaning that calculations involving summary tasks will often suffer.
Thank you, this was helpful ! I am working on another option and if I win I will share it with you. Thank you for your help.
Regards
Hi Safford where can I obtain more information regarding the “language” to understand the writing of these formulas?
Regards
Microsoft has a useful support article called Project Functions for Custom Fields. This will give you a basic overview of the different types of functions that can be used in formulas. Many of these functions are used in the examples above if you want to see how they work in the real world.
Hello Safford, I am looking to create a custom filter for displaying cumulative to status date planned tasks, which have either an early finish, late finish, or on time to the baseline dates. I have tried several attempts, but am coming up short. I am using MSP 2010. I need this to create an export to Excel/Access using VBA mapping. If there is an easier way using auto filters, or built-in filters, I would like to know how to accomplish. I would prefer using a custom filter to populate global templates. Thank you in advance for your help.
Thanks for your question, Paul. Can you clarify what you mean by “displaying cumulative to status date planned tasks”?
Hi,
I generate a weekly slippage report in project.. using
[Finish]-[Baseline1 Finish]
I want to include a condition to return 0 where slippage value is less than 1 and 0 where value is less than 0
So there would be 3 parameters here
1 Returning the Value when > 1
2 Returning 0 when value is less than 1
3 Returning 0 when value is less than 0
In this case, we can combine your conditions #2 and #3, since something that is less than zero will also be less than one. I also assumed that slippage equal to one should show a value, so I tested for slippage to be greater or equal to one. This gives us the following formula:
IIf(([Finish]-[Baseline1 Finish])>=1,([Finish]-[Baseline1 Finish]),0)
Thanks Safford, That was very good….
Hello!
I am using a Master Project to track each project and the respective sub-tasks, typically around 50 sub-tasks per project, the order of sub-tasks is not typical).
My manufacturing departments would like the “Ship Date” sub-task to be visible even when the project is collapsed, is this possible?
My initial thought was to add a column (Flag1) with a formula that automatically picks up the “ship date” task end date information. Does this make sense? How would you go about writing this formula?
Unlike Excel, Project does not allow formulas to access data in different rows. If you collapse the subproject so that only the summary task shows, there would not be a way to dynamically show the “ship date”, since it is in a different row than the summary task and cannot be accessed by a formula.
If you are trying to build a report for your manufacturing department, you could use OnePager Pro to give them what they need. OnePager would allow you to show the summary task for each project plus the ship date without having to show any of the other intermediate details. Depending on your requirements, you could create more of a Gantt chart where the summary task is on one line and the ship date is underneath, or you could create a timeline where each project gets a single row and the ship date is attached to the main summary bar on the right-hand side as a milestone.
Where can I find more information on MS Project formulas (syntax, structure and usage). Thanks. Currently subscribed for the 14 day trial. Thanks again,
Charlie Cirronella
Hi Charlie,
Since you are a OnePager trial user, you are eligible for free tech support, which includes help with using a Microsoft Project formula. I have sent you a direct e-mail with my contact details so that we can connect offline and walk through some of your questions.
-Safford
Hi Safford
I hope you can help me…I have a End Date Health Check which is based on the graphical indicators. I want to use the finish date and the Status Field to populate the results in the End Date Health Check column.
The Status column is based on:
On Track – 1 (Green)
Complete – 2 (Blue)
Uncommitted – 3 (Grey)
Not Finished with 2 weeks to finish date – 4 (purple)
Not Finished with more than 2 weeks to finish date – 5 (pink)
Delayed – 6 (yellow)
Risk – 7 (Red)
Many thanks….
CCB
A Microsoft Project formula is always going to be based on the data in the field itself, not the graphical indicators. However, you can always apply graphical indicators in MS Project or in OnePager Pro based on the data that a formula calculates, or vice-versa. For example, OnePager Pro can use conditional formatting to look at the “Purple” result of the formula and actually paint the task or milestone purple in your Gantt chart.
For your example, I’m using “Text1” as my status field, but if you are using something different, you can swap it in anywhere you see “Text1” in my formula:
IIf([Text1]="On Track", "Green",
IIf([Text1]="Complete", "Blue",
IIf([Text1]="Uncommitted", "Grey",
IIf(([Text1]="Not Finished" AND ([Finish]-NOW()) <= 14), "Purple",
IIf(([Text1]="Not Finished" AND ([Finish]-NOW()) > 14), "Pink",
IIf([Text1]="Delayed", "Yellow",
IIf([Text1]="Risk", "Red", "No Color"
)))))))
For the most part, this formula looks at the value of the status field and assigns a color. However, for tasks that are not finished, we add a second condition to determine whether the difference between the finish date and today is more or less than the two-week threshold.
Hi Safford
Thank you so much for this formula – sadly there is an error message I am having ‘the formula cannot be saved because it creates a circular dependency with the “(Text3)” – your Text 1.
Many thanks
CCB.
It sounds like you might have accidentally placed the formula in your status column instead of in a separate column that then references your status column. Much like Excel, a Microsoft Project formula in Column A cannot make any reference to Column A as a part of the calculation. This is the cause of the circular reference warning that Project is giving you.
In my example, Text1 is the status field and Text2 is the resulting color calculation. The formula resides in Text2, but references Text1 and Finish to perform the color calculation:
Apologises Safford – it works perfectly! it was ‘user’ error.
Thank you so much for this.
All the very best,
CCB
Safford, Good day. I’m looking for a pretty simple formula to flag durations greater than 20 days.
This one is fairly simple, though it does have a couple of necessary tricks. First and foremost, Microsoft Project stores durations as text like “20 days”, so we need to convert those to numbers before doing any kind of comparison.
The 480 constant in this equation assumes an eight-hour workday, and then 60 minutes in an hour. If you have workdays of a different duration, or are measuring time down to the second, you may need to play with the 480 number to get things to calculate correctly.
Here is the formula:
IIf(ProjDurValue([Duration])/480>20,"Yes","No")
Here is a formula that will take a numeric field (Cost1) and convert it into a string (Text30) with a currency format. This is useful if you need for force-format a number field into a specific format that isn’t impacted by MS Project’s regional or cultural settings:
Format([Cost1],"$#,##0.00;($#,##0.00)")The red section is a custom numeric format string that you can modify if you want to remove comma separators, remove cents, or change the currency symbol.
Hi! Is there a OnePager user group / forum?
Yes. We have a OnePager User Group on LinkedIn. If you request to join the group, we’ll be happy to approve you for membership!
Hi,
I want to make a formula that will set the flag according to my milestones. Currently i tried IIf([Task Mode]=[Milestone],”Yes”,”No”), however this formula adds “No” to the milestones. If I switch yes and no, the flag is set to “Yes” for the milestones, but i don’t know why, so to be sure that i am using the right formula i was hoping that you could give me an explanation?
Also, is there a formula that can set the flag according to when resources with similar names are being used? -they all include “QC”… The same goes for the tasks; i have repeating tasks named QC, is there a formula that can set the flag according to the text in the task name field?
I just signed up for the limited trial, which explains the number of questions.
I hope you can help me out, and i am looking forward to your reply.
For milestone-based filtering, you don’t really need to create a custom formula in Project. Instead, you can change OnePager’s filtering from “Flag20” to the “Milestone” field in the import wizard. This will automatically bring milestones in without you having to do anything ahead of time in Project itself:
That said, if you ever DID need to find out which items are milestones, your formula needs to look at the Milestone field from MS Project instead of the Task Mode field:
IIf([Milestone],"Yes","No")For your second question, this formula will flag all tasks where the value “QC” appears in the Resource Names field:
IIf(InStr([Resource Names],"QC"),"Yes","No")Of course, you can swap out the MS Project field and the search value to suit your needs for different filters.
Many thanks!
I want a formula that I can group in Look ahead view. I want to group it by late, Due in 1 week, Due in 2 Weeks , Due in 3 Weeks,
Due in 4 Weeks , Due in rest of whatever. I want a formula that will provide this in text so I can group it by the text. Thanks in advance
You could do this by comparing the finish date to the deadline date, comparing the finish date to today’s date, or comparing the deadline date to today’s date. Essentially, the formulae are the same, but it’s a question of which two values you are comparing.
Compare Today’s Date to Finish Date
IIf([% Complete] < 100,Switch(
[Finish] - Now() < 0, "Late",
[Finish] - Now() < 7, "1 Week",
[Finish] - Now() < 14, "2 Weeks",
[Finish] - Now() < 21, "3 Weeks",
[Finish] - Now() < 28, "4 Weeks",
[Finish] - Now() >= 28, "Farther Out"
), "Finished")
I’ll spare everyone the repetitiveness of showing the other two date comparisons, since the concept is the same. One other layer you might consider is whether you want to look at percent complete as a part of the calculation. My formula above does consider percent complete. Tasks with finish dates in the past aren’t late if they’re already finished, which is why I wrapped it in a condition that checks percent complete as well to make it even more accurate.
Once you’ve tagged tasks as being due within a given timeframe, you can use OnePager to group them into swimlanes and color-code each status so that it’s easier to tell what is due when (click to enlarge):
Please check my formula. This is for targeted % complete for the week. We update week ending every Thursday so its based off of status date. Also I use physical % complete for earned value purposes.
IIf([Baseline Start]>[Status Date],0,IIf([Baseline Finish]<[Status Date],100,projdatediff([Status Date],[Baseline Start],IIf([Task Calendar]="None",[Project Calendar],[Task Calendar]))*100/projdatediff([Baseline Finish],[Baseline Start],IIf([Task Calendar]="None",[Project Calendar],[Task Calendar]))))
Syntactically and logically, it appears correct. Your formula will return an error if there is ever an “N/A” value in either of the two baseline fields, so you might consider bullet-proofing against that. Of course, if you’re setting the baselines in Project automatically, the chances of a null baseline date are pretty slim, so it may not be worth the added complexity.
Hi Safford,
I am looking for %Planned complete vs %Actual Complete,
Your August 18, 2015 at 12:40 PM Post is very useful to me however the given formula is capturing weekend days also, could you pls help me with the formula excluding Saturday & Sunday.
Thanks in advance
You can use ProjDateDiff instead of DateDiff if you want to account for weekends:
ProjDateDiff([Start],[Finish])/480
ProjDateDiff looks at the Microsoft Project calendar, so depending on how that calendar is configured, you may have to adjust the calculation to give you the correct number.
For example, my MS Project calendar has an eight hour workday, meaning that each day is valued at 480 minutes.
If I have a two-day task, ProjDateDiff by itself will give me a result of 960 working minutes. Dividing that by the 480 constant gives me the more useful result of two working days.
I’m looking for some help/guidance to create a Project report that shows Slipping Tasks based on % Complete compared to the Duration. Does anyone know of a formula that looks at a task that is say, 4 weeks in duration and if we’re currently halfway through the duration but the %Complete is less than 50%? The purpose is to flag any task that is falling behind schedule purely based on what is done so far and what is expected to be done to date.
In most cases, you don’t need a formula to do this. Instead, you can use Microsoft Project’s built-in “Status” field, which looks at your project status date and compares it to your percent complete to determine whether you’ve completed enough of a task on a given date to be ahead or behind schedule.
You can pull this status field from Microsoft Project into OnePager Pro to color-code your report. For example, late tasks are red, complete tasks are green, etc. This makes it easier to quickly identify tasks that are in trouble.
Great blog! I was wondering if you might possibly be able to help me with a formula. So far I have been able to customize my ECF’s and have them working the way that I need but now leadership wants to push a big further of course. So I have a Task Level field that captures the Health I.E. Upcoming Task, Future Task, Late, On Schedule, At Risk, Complete and On Hold in addition to a matching indicator field. On Hold is handled with another column that if the integer 1 is selected in that column it updates the Task Health and indicator value to On Hold, precisely what I wanted it to do. However, if a task or milestone is placed on hold the values there before marking on hold still impacts the overall schedule, so if that task is running late when being placed on hold it still has impact. We do NOT want it to have impact. Proposed Scenario: Use another CEF to automatically capture the % Complete value to store until the task is made active again and update % complete to 100%. I have tried various ways to make this happen, workflow, modifying the current formula for the Task Health, etc…nothing is working. I am wondering if you might have an idea for a formula that would work.
This is best handled by VBA instead of a formula. The reason is that when you write a formula based on the value of your on-hold column, both the %Complete and the “Backup %Complete” are going to be changed simultaneously, which will either cause both values to be zero (not good) or both values to be non-zero (equally not good). Plus, if you have two formulae referencing each other in opposite columns, you could end up with a circular reference issue, causing errors in both fields.
With VBA, you can control the sequence with which values are swapped. For example:
1. When On-Hold changes to 1,
a. Copy %Complete to %CompleteBackup so that you’ll have record of what it was.
b. Set %Complete to zero so that it doesn’t impact your overall project status calculation.
2. When On-Hold changes to 0,
a. Copy %CompleteBackup back to %Complete so that it will have impact again.
b. Zero out %CompleteBackup, though this isn’t entirely necessary.
Hi , how to calculate the % plan for every month using MS Project , need to perform the S Curve .
I am using MS Project 2010.
Hi there, R. If you can clarify your question a little more I might be able to help. What do you mean by calculate the % plan? With regard to your S-curve, what is the variable that you’d like to display over time? One thing you might want to check out are the out-of-the-box reports in Microsoft Project that leverage the strong standard graph abilities in Excel. Here is a video via MPUG, and here is a Microsoft article on the subject.
Hello,
I need to create a filter to find some specific subtasks starting in the Next Week.
My Current filter is {Resource Names equals “A Text String” AND Name equals “Another Text String”} AND {Start is less than or equal to “End of the next week is:”? AND Start is greater than or equal to “Start of the next week is:”?}
This leads me to choose the next Friday and next Monday from the pop-up.
So, I decided to create Custom Date fields using formula to get next Monday and Next Friday.
The formulas are as follows:
NextMonday=Now()+9-Weekday(Now())
NextFriday=Now()+13-Weekday(Now())
Now I tried to link these custom fields to the filter as follows:
{Resource Names equals “A Text String” AND Name equals “Another Text String”} AND {Start is less than or equal to [NextFriday (Date9)] AND Start is greater than or equal to [NextMonday (Date10)]}
But, I am getting the error message, The entry is not valid. The test value cannot be used with the field for the data you want to find or filter for.
Please help me.
May be there is an easy way to do it!
Comparing date fields in a filter is hard to do. You’ll have an easier time comparing the date fields in a formula, and then creating a simple filter based on the result of that formula.
For example, I can use “Flag1” in Microsoft Project to store the result of the following formula:
[Resource Names]="A Text String"
AND [Name] ="Another Text String"
AND [Start] >= [Date9]
AND [Start] <= [Date10]
I noticed one other issue with your calculation of “Next Monday” and “Next Friday”. The Now() function gives you BOTH a date and a time. So, next Monday isn’t just July 9th. Rather, it is July 9th at 1:37 PM, or whatever time you happen to have the project plan open. The specificity of time here will cause problems with your formula because a task that starts on July 9th at 8:00 AM will be left out of the filter that begins on the same day at 1:37 PM.
To get around this, you need to revise your calculations for next Monday and Friday to consider working time. Here, we add eight hours (from midnight) to Monday and seventeen hours to Friday:
Next Monday: DateAdd('h',8,DateValue((Now()+9-Weekday(Now()))))
Next Friday: DateAdd('h',17,DateValue(Now()+13-Weekday(Now())))
With this level of specificity, a task starting on July 9th at 7:59 AM would be excluded, as would a task starting on July 13th at 5:01 PM. You may or may not need this amount of precision, but it’s available in the formula, which gives you quite a bit more flexibility than the filter does.
Hi,
I’m using MSProject 2016, is there a way to show on the dashboard built in on MS- Project the count of activities that are completed, late and on schedule? it always shows the percentage only.
I can’t speak for MS Project’s native dashboard, since OnePager is an integrated dashboard for MS Project in its own right.
But in general, you could write a formula that populates a number field with a 1 when a child task is complete and a 0 when it isn’t. For the summary tasks in your schedule, instead of having that same formula re-calculate based on the status of the summary task itself, have it sum the formula results of its children, and it will give you an accurate count instead of a percentage.
We have an article on how to count completed project tasks, including an example formula and how to get the summation to work at the top of the project plan, if it’s helpful.
Good day Safford
Like your work. I need some help, please.
I have a cost loaded schedule with weighting for each task. I am updating manually currently with physical % complete. I need a way to calculate the remaining duration based on the physical % complete achieved to date.
Is there a formula I can use for this
Hi Eon,
Do the task duration and physical percent complete in your project have a linear relationship? In other words, does a task that is scheduled for 10 days and is 50% complete have 5 days remaining? We see projects where this relationship is not linear, but if yours is, I should be able to help you with a formula.
Hi Safford ,
Thanks for the planned% formula provided.
Indeed in the formula you used “Now” today date , to find planned% .
I want to find planned% until status date , not today. what modification do I need ?
Cheers,
You can access the status date in a formula by treating it as a field, even though it’s really not one:
[Status Date]So, the revised formula for this portion would be:
DateDiff("d",CDate([Baseline Start]),CDate([Status Date]))Hi Safford ,
% planned formula , gives result as 62.2548785% , how to make it integer for ex. 62% ?
Many thanks
Cheers,
I just found the answer to my question above ; just put before the code :
Int(
and after the code
)
I was wondering if there is a way to create a formula that could tag and/or flag a MS task if its driving predecessor is critical. Our schedule practice has us tie the tasks together and not tie it through the MS. For example:
1) Not to use:
Task 1 > MS > Task 2
2) To use:
Task 1 > Task 2
Task 1 > MS
In the second example the MS would not be flagged as ‘critical’ even though Task 1 and Task 2 are. So i would like to use a formula to tag/flag the MS if the driving predecessor is critical.
Any assistance would be appreciated.
Unfortunately, this is not possible. A Microsoft Project formula can only reference fields on the same line for the same task. A formula cannot reference fields that pertain to a different task. In this sense, it’s quite a bit more limited than Excel, where a formula can reference any cell in the worksheet.
Ok, thank you so much for your reply and being so quick about it.
Hello Safford,
What I’m trying to do is to count scheduled hours for each task for range from the first and the last day of month (e.g. from 2019-12-01 to 2019-12-31).
So, I created field [Text1] with different statuses, where one of them is “Scheduled’. Then I created field of [Number] type and put the following formula there: IIf([Text1]=”Scheduled”;[Work]/60;”0″)
It does calculation for finish date but I need for range of one month. And I cannot figure out how to do that, what should I put in formula. Tried dozen of options already.
Is there a way to do that?
Thank you very much for any assistance.
The value in the Work field is based on the total task duration, so you won’t be able to use a formula to pull it for a shorter duration such as a month. You might be able to do it in VBA by doing a summation of scheduled resources for a given time period, but a formula wouldn’t be able to do this for you.
Thank you very much! Appreciate it.
Hi All,
I have one challenge that i want create a field that is to tag level 3, 4 and 5 line items with the level 3`s task name.
Regards,
Akito
This cannot be accomplished with a formula in Microsoft Project, unfortunately.
Hi, this formula throw an Error, please can you checkit? I think is tha amount of levels of the function IIF.
IIf( ([Fecha actual] 0 AND [% trabajo completado]=[Comienzo de línea base estimado]); “Iniciada a tiempo”; IIf( ([Comienzo] > [Comienzo de línea base estimado] AND [% trabajo completado]>0 AND [% trabajo completado]=[Comienzo de línea base estimado]); “Iniciada con retraso”; IIf( ([Comienzo] 0 AND [% trabajo completado]=[Comienzo de línea base estimado]); “Iniciada con adelanto”; IIf( ([Fin] = [Fin previsto] AND [% trabajo completado]=100); “Concluida a tiempo”; IIf( ([Fin] > [Fin previsto] AND [% trabajo completado]=100); “Concluida con retraso”; IIf( ([Fin] [Comienzo de línea base estimado] AND [% trabajo completado]=0); “Retrasada sin iniciar”; “Retrasada”))))))) )
You are probably missing a bracket, parenthesis, or operator (equals, greater than, less than, etc.) somewhere. To your point, it’s very hard to tell due to the large number of IIF statements. A cleaner way to write this formula would be to use the SWITCH function instead of IIF, which will give you the same logic, but in a much easier-to-diagnose format.
If the SWITCH still doesn’t work, then it’s more likely to be an issue with the syntax of one of your operators. You can debug that by splitting the formula temporarily into smaller formulae and testing each one individually to find the issue.
hellow,
i want to display last update % comp in text 10 column in my schedule.it was helpful to understand the difference between last update and latest update please suggest me formula.
Hi all,
I am using project 2019 professional. I’m stuck on how to draft a formula for calculating the total amount of FS logic in a schedule. I can figure out how to make it a percentage of the overall project but I cannot get any formula to recognize 1) FS and 2) activity numbers within predecessors and successors fields. Any help is greatly appreciated.
I can show you how to write a formula to parse the Predecessors field to get the amount of delay between finish-to-start dependencies and how to get the Unique ID of the predecessor task:
To get the UID of the predecessor task for any successor task in a text field:
Left([Predecessors],IIf(InStr([Predecessors],"FS")>0,
InStr([Predecessors],"FS"),
1) - 1)
To calculate the amount of time between when the predecessor finishes and the successor starts in a number field:
IIf(InStr([Predecessors],"+")>0,mid([Predecessors],(InStr([Predecessors],"+")+1),(InStr([Predecessors],"d")-InStr([Predecessors],"+"))-1),
0)
With these calculations in place, Microsoft Project 2019 can sum all of the latter numeric values up at the Project Summary Task level to give you a project-wide figure. You could get fancier here by allowing for both positive and negative time based on whether the Predecessors field says 1FS+30d or 1FS-30d. My formula assumes that all elapsed time is positive and all dependency relationships are finish-to-start. If you need to consider the other dependency types or negative time, you could do so with a Switch statement, replicating my basic formula a few more times to cover the different permutations. Hopefully your schedule isn’t that complex!
Hi,
I need to create a weekly report for tasks due weekly (based on baseline finish date during the week), tasks complete, cumulative tasks due and cumulative tasks completed.
I have created a custom field for Tasks count, and another custom field for 100% complete tasks. These two take care of first two requirements. I am struggling to create the cumulative weekly field formula for due and completed tasks.
Many thanks
Sachin
W
If you want to calculate a cumulative count or sum, you would need to first write a formula that places a 1 or a 0 in a given number field based on the condition that you’re looking for. In your case, you could write one formula that assigns a 1 when a task is 100% complete, and a 0 when not. Your second formula would assign a 1 when a task’s baseline falls within the current calendar week and a 0 when not.
Once you have those two values on a line-by-line basis, you can tell Microsoft Project to summarize the values of each line and record it on the Level 0/Project Summary Task. Do this by changing the Calculation for Task and Group Summary Rows setting from None to Rollup with Sum selected in the dropdown:
Then, when you turn on your Project Summary Task you will see the summarized value for the entire project:
Great post. I am looking to define a formula that populates the Level 2 task/summary description on each of the tasks below it. This will enable me to filter on it. So, if I have a WBS. 3.2.1.1 to 3.2.1.10, they each have a custom field with the description of the WBS line of 3.2
You could try using the Task Summary Name field which is something that Microsoft added in Project 2019 and newer releases of Project 2016. However, this is only going to give you the summary name for the parent task that is one level above (n – 1) a given task. So, if all of the tasks in question are at outline level 3, it would give you the level 2 parent without any issues. However, if you also have some tasks at outline level 4, it’s going to give you the level 3 parent instead of the level 2 parent for those.
Unfortunately, MS Project remains pretty limited when it comes to using a formula to try and reference data from neighboring tasks. This is one of the biggest differences between Excel and Project, since Excel lets you write a formula that can reference any row and any column, whereas a formula in Project is typically only going to reference columns within the same row.
I just want to know formula to convert duration of each milestone / task into percentage
Hi Arif,
I can try to help with this, but need a little more information from you first. What is the other value you want to compare the duration to in order to derive a percentage? What are you planning to use as your numerator and denominator?
I put a “Stoplight Report” column into my project and the formula below was available from another project (using graphical indicators: 5 = blue for complete, 4 = red for behind, 3 = yellow for behind, 2 = green for on time, 1 = no assigned color).
IIf(Duration=0,(IIf([% Complete]=100,5,IIf(Finish<[Status Date]+7 And [% Complete]=80,3,IIf(Finish<[Status Date]+7 And [% Complete]=100*(Abs(ProjDateDiff(Start,[Status Date])/ProjDateDiff(Start,Finish))),2,(IIf(Finish>[Status Date],IIf(Start>[Status Date],1,3),4)))))
When I asked the PM what # of days behind did he want me to put for red and yellow (so I would change the 7’s in the formula to his desired # of days), he wanted something different than what this formula captures. He wants critical path tasks that are behind one day to be red and all other tasks that are behind X # of days to be yellow.
How do I rewrite or tweak this formula to incorporate the Critical aspect and differentiate those tasks from the rest? Thanks.
Hi Anne,
Your formula will probably be easier to manage if you use the SWITCH statement instead of nested IIF statements. I see some use of PROJDATEDIFF, which is good, but it’s not being used consistently, so you might look into that to make sure the date differences are being calculated accurately under all conditions.
Another recommendation is to have the results of your formula write to a text field instead of a number field. This way, instead of trying to remember what “5” means, you’ll know it means “Complete”.
Here is an example of a similar formula that you can borrow from. It looks at the CRITICAL field in MS Project, and only when critical, looks at the difference between today’s date and the finish date to determine when things are a little late, or very late. We accomplish this by testing for criticality AND some secondary condition in each clause of the SWITCH statement.
Switch(
[Critical]=FALSE,
"Non-Critical",
[Critical]=TRUE AND [% Complete]=100,
"Critical/Complete",
[Critical]=TRUE AND (ProjDateDiff([Finish],Now())/480) <= 0, "Critical/Not Due Yet", [Critical]=TRUE AND (ProjDateDiff([Finish],Now())/480) <= 1, "Critical/Slightly Past Due", [Critical]=TRUE AND (ProjDateDiff([Finish],Now())/480) > 1,
"Critical/Very Past Due",
)
By the way, you can feed the results of this formula into OnePager Pro, which will enable you to have the status-driven coloring in the body of the chart itself, as opposed to just in a graphical indicator column in the MS Project grid:
Thanks for the Switch formula. I just barely know enough about coding and complicated formulas to be dangerous, so have never seen this function. I can understand most of what the formula does, but what’s the significance of the 480?
Also, how do I account for the non-critical path tasks which also need to be a part of the stoplight report? At first glance I assumed I could just do a repeat by replacing [Critical] with [Non-critical] and the appropriate number of days instead of 1, but now that I take a second look I realize that the initial [Critical]=FALSE,
“Non-Critical” most likely won’t allow that. So, my next assumption is I’d have to do [Critical]=FALSE AND then the rest:
Switch(
[Critical]=FALSE,
“Non-Critical”,
[Critical]=TRUE AND [% Complete]=100,
“Critical/Complete”,
[Critical]=TRUE AND (ProjDateDiff([Finish],Now())/480) <= 0, "Critical/Not Due Yet", [Critical]=TRUE AND (ProjDateDiff([Finish],Now())/480) 1,
“Critical/Very Past Due”,
[Critical]=FALSE AND [% Complete]=100,
“Non-Critical/Complete”,
[Critical]=FALSE AND (ProjDateDiff([Finish],Now())/480) <= 0, "Non-Critical/Not Due Yet", [Critical]=FALSE AND (ProjDateDiff([Finish],Now())/480) 7,
“Non-Critical/Very Past Due”,
)
If this is not correct, please let me know.
Thanks so much for your help on this. None of the instructional websites I looked at to try to learn how to do this got into formulas as complicated as this one. Thanks!
Anne.
The 480 comes from an 8 hour workday times 60 minutes in an hour. The PROJDATEDIFF function returns work minutes, which is pretty useless, so by dividing that by 480, you get work days instead. Not exactly intuitive, I know.
The [Critical] syntax refers to a field from Microsoft Project that is literally called “Critical”. There is not a field called “Non-Critical”, so you cannot include [Non-Critical] in your formula.
The [Critical] field can either be TRUE or FALSE, so you can change your logic as you’ve done in the example above to test for a TRUE or FALSE condition and then add secondary conditions with an AND as necessary to cover the different statuses that you want to track.
It’s usually a good idea to set up a sample project plan with 5-10 tasks where you know what the status is supposed to be. Test your formula against those known tasks first and make sure that you get the desired result. Pay special attention to your operators like greater-than, less-than, greater-than-or-equal, and less-than-or-equal, as these can make or break a formula on those border conditions.
So I guessed right, and the formula as I wrote it should work?
Thanks for all the explanations.
By the way, my coworker has used OnePager Pro and loves it. He said you personally helped him quite a bit in his last position since there were quite a few unique conditions in the computer systems. He had nothing but good to say about you. 🙂
Your formula looks pretty close. Based on my quick review, I see two cases where you are missing an operator.
There is a trailing “7” here, which looks like it’s missing a greater-than or less-than sign:
[Critical]=FALSE AND (ProjDateDiff([Finish],Now())/480) 7There is also a trailing “1” here, which also looks like it’s missing an operator:
[Critical]=TRUE AND (ProjDateDiff([Finish],Now())/480) 1This may just be a function of copying your formula into the blog comments, which can sometimes strip out the occasional character, so if your local version of the formula doesn’t look like it’s missing a greater-than or less-than sign, you should be in good shape!
Hi Guys,
I’m wondering if its possible to create a formula field that only displays text of a certain Outline level. Similar to the “Task Summary Name” but one level up.
Hope this makes sense, look forward to your response.
Unfortunately, this is not possible in Microsoft Project. However, you can achieve it in OnePager Pro, by displaying the Level X Summary Name as text in the body of your chart.
Amazing. Your understanding is incredible.
I have a task that has a manual start date of TBD.
A successor task that automatically starts 1day after.
Every week I need to send out schedules for the coming week.
The task with TBD is fine.
Is there a formula for filtering the successor task date if the predecessor is not TBD. I just want a task list of the items not connected to TBD.
Is that possible?
It is not possible to filter on the predecessor task, because a Microsoft Project formula can only look at the current row of the schedule, and cannot look at other tasks above or below the current task.
However, you could write a formula to look at the dates on the successor task and filter them for any values that don’t contain “NA”.
I am trying to write a formula for tasks where there is a change in the finish date for tasks that are not 100% complete. This is to make the team aware of tasks that have had a date changes. My project plan has a start and finish dates, % complete and duration along with various other fields.
You should establish a baseline in Microsoft Project first. Once baselined, you can compare the Baseline Finish field to the Finish field for any tasks that are incomplete. You could also look at the Finish Variance field and then use conditional formatting in OnePager to call attention to these problem tasks:
Here’s an example that is very close to what you’re trying to do:
https://www.onepager.com/community/blog/identifying-late-and-very-late-tasks-with-finish-variance/
The only difference is that this formula looks at the Finish Variance for all tasks regardless of %Complete, so you’d want to modify the formula to only look at incomplete tasks.