Microsoft Project Formulas: the Smart Way to Filter Tasks for OnePager Pro

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.

formula_rollup

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!

Download a 15-day free trial of OnePager Pro

This entry was posted in Best Practices, Microsoft Project Tips, Project Reporting by Safford Black. Bookmark the permalink.

About Safford Black

Safford is a versatile technology professional with a solid history of empowering emerging growth companies in a broad array of industries. His employment history includes energy industry consulting at Quorum Business Solutions, Senior Manager of Business Development and Technical Sales at telecom service aggregator GetConnected, and Vice President of Strategic Partner Management at electronic payment processor IP Commerce. Prior to his tenure as OnePager’s COO, Safford was the company’s Vice President of Marketing and Alliances. Safford holds a BA in Psychology and management from Rice University.

55 thoughts on “Microsoft Project Formulas: the Smart Way to Filter Tasks for OnePager Pro

  1. 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.

  2. 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.

  3. 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.

  4. 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?

  5. 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.

  6. 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?

  7. 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.

  8. 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!

  9. 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.

  10. 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.

  11. 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.

  12. 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

  13. Hi Safford where can I obtain more information regarding the “language” to understand the writing of these formulas?
    Regards

  14. 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.

  15. 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)

  16. 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.

  17. 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

  18. 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:

          Microsoft Project formula: Circular reference warning

    • 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")

  19. 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.

  20. 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.

  21. 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):

  22. 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.

  23. 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.

  24. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *