none
Custom field - Split the duration per year, no min max formula? RRS feed

  • Question

  • Hi,

    I am relatively new to MS Project and I want to split the duration of all tasks per year. My projects runs for three years and I*d like to have a custum column with the duration for each year 2016, 2018, 2019. While this is easy in excel with a Min, Max function I can*t find a way in MS Project. So far I can only get the whole duration with 

    ProjDateDiff([Start],[Finish],"standard")/480

    And This is an unsuccessful attempt for 2016

    ProjDateDiff([Start],"12/31/2016","standard")/480

    Is there really no math function max and min? I need to get the max of Start and beginning of  2016 and the min of End and end of 2016. 

    Thanks a lot in advance!


    • Edited by TobiOneQ Saturday, February 13, 2016 5:42 AM
    Saturday, February 13, 2016 5:42 AM

All replies

  • So, I did it but it looks ugly :

    IIf(Year([Start]) > 2016 OR Year([Finish]) < 2016, 0, IIf(Year([Start]) < 2016 AND Year([Finish]) > 2016, ProjDateDiff("1/1/2016","12/31/2016","standard")/480, IIf(Year([Start]) < 2016 AND Year([Finish]) = 2016, ProjDateDiff("1/1/2016",[Finish],"standard")/480, IIf(Year([Start]) = 2016 AND Year([Finish]) > 2016, ProjDateDiff([Start],"12/31/2016","standard")/480, IIf(Year([Start]) = 2016 AND Year([Finish]) = 2016, ProjDateDiff([Start],[Finish],"standard")/480,999999)))))

    Is there a better way? 

    Saturday, February 13, 2016 6:53 AM
  • TobiOneQ,

    My first question is, what happened to 2017?

    My second question is, why do you want/need a separate field to define durations of each of the years? If you have a multi-year project, and you need it broken out by years, the best practice is to have a separate summary for each year's tasks. The Project Summary Task (Format>Show/Hide.Project Summary Task) will display the whole project.

    On the other hand, if you have you heart set on the custom field, then yes, there might be other ways to parse out the years in a formula, but if the one you have works, I'd stick with it.

    Hope this helps.

    John

    Saturday, February 13, 2016 4:19 PM
  • That's about it. If you want to be really correct, you should use the DateSerial function instead of hard-coding date strings (won't work on computers with different date formats), and not assume that the calendar is "standard." Like this:

    IIf(Year([Start])>2016 Or Year([Finish])<2016,0,IIf(Year([Start])<2016 And Year([Finish])>2016,ProjDateDiff(DateSerial(2016,1,1),DateSerial(2016,12,31),IIf([Task Calendar]="None",[Project Calendar],[Task Calendar]))/480,IIf(Year([Start])<2016 And Year([Finish])=2016,ProjDateDiff(DateSerial(2016,1,1),[Finish],IIf([Task Calendar]="None",[Project Calendar],[Task Calendar]))/480,IIf(Year([Start])=2016 And Year([Finish])>2016,ProjDateDiff([Start],DateSerial(2016,12,31),IIf([Task Calendar]="None",[Project Calendar],[Task Calendar]))/480,IIf(Year([Start])=2016 And Year([Finish])=2016,ProjDateDiff([Start],[Finish],IIf([Task Calendar]="None",[Project Calendar],[Task Calendar]))/480,999999)))))

    Tuesday, February 16, 2016 2:46 PM
  • And to be really, really correct, you should substitute [MinutesPerDay] for the 480's above, in case your project has something other than eight hour days.
    Tuesday, February 16, 2016 7:20 PM
  • Great, thanks a lot John and Bill. I indeed had the issue with the German format. 

    But you are right, since I am new to MS Project this was probably a bad idea to break down per year. What I have is a simple MS Projects where everything has been planned in the task view - duration, cost, resources, etc for each task and now I'd like to be able to see the breakdown per year since the tasks go from 2016 through 2018.

    As far as a get it I should create three summary tasks encompassing the whole project for each year and show and hide them as needed. If this is correct I am going to read in this topic. 

    Thank you very much for the help, much appreciated!

    Wednesday, February 17, 2016 7:02 AM
  • Not sure how you'd handle tasks that run from one year to the next. A task can't be under two summary tasks, as far as I know (2016 and 2017, say).
    Wednesday, February 17, 2016 2:35 PM
  • That is why I wanted to create extra fields that break down the budget per year but then it turned out I can have only up to 20 such fields and I already have 15 taken. I have 5 inputs (different resources) to break down and this time 3 years makes at least 15 entries.

    In addition all tasks have their own summaries (phases) that also run over multiple years and they have to be represented as well. But the goal is to be able to get a report what are the expenses for each year. I am not sure but probably I have to split the project into resources as well.

    Wednesday, February 17, 2016 2:46 PM
  • TobiOneQ,

    Have you looked at the Resource Usage view? You can set the timescaled data (right side) to be in years. That should provide an easy yearly summary for work, cost, etc. Note, you can also display the Cumulative Work and Cumulative Cost to see how those values build over the 3 years.

    John

    Wednesday, February 17, 2016 3:51 PM
  • John,

    thank you very much for the advice. Since we do not have a view there and everything was hard coded on the tasks fields I think that I should go and create a real resources view that can be the broken down as you have suggested.

    Have a great weekend!

    Friday, February 19, 2016 8:23 AM