locked
Custom Field: Tasks date difference excluding "Holidays and Weekend etc." RRS feed

  • Question

  • Dear All,

    I have to create a custom field which will calculate the tasks date difference between two different tasks.

    Now suppose a Project with name: ProjectA , which has three tasks "TaskA, TaskB, TaskC ".

    TaskA: Start Date: 3/1/2011 FinishDate : 5/1/2011.

    TaskB: Start Date: 6/1/2011 FinishDate : 10/1/2011.

    TaskC: Start Date: 11/1/2011 FinishDate : 13/1/2011.

    Now I want to find date difference only between TaskA: Start Date: 3/1/2011 and TaskC: FinishDate : 13/1/2011  excluding weekly offs and holidays etc.

    Like difference should come as 8 days instead of 10 days excluding weekend on Sat and Sun on 8th and 9th Jan respectively.

    How can I create a task level custom field to achieve this.


    Sachin Vashishth MCTS
    Wednesday, January 5, 2011 3:09 PM

Answers

  • Sorry for misunderstanding, didn't noticed you wanted to retrieve difference between 2 different task i.e. Task A & C ,based on calendar, Formula in task level custom field won't work, And since your project template is fixed, it can be done using simple Macro, you may further extend it to suit your needs 

    Sub DateDiff()
        Dim ts As Tasks
        Dim t As Task
        Dim Startx, Finishx As Date
        Dim dur As Integer
        Set ts = ActiveProject.Tasks
        
        Set t = ts(5) 'Replace 5 with task index
        Startx = t.Start
        Set t = ts(7) 'Replace 7 with task index
        Finishx = t.Finish
        dur = (Application.DateDifference(Startx, Finishx, Standard) / 480)
        MsgBox (CStr(dur) & " " & Days)
    End Sub

    Hope this helps :)

     


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com
    Wednesday, January 5, 2011 6:05 PM

All replies

  • Hi Sachin,

    there is a function for that: ProjDateDiff( date1; date2; calendar ).
    Use your calendar (assuming holidays are added and weekends not deleted). The difference will be calculated as you want to.

    Regards
    Barbara

    Wednesday, January 5, 2011 3:14 PM
  • Sorry, I think you will have to replace ";" by ",": ProjDateDiff( date1, date2, calendar )
    • Proposed as answer by epmXperts Wednesday, January 5, 2011 4:45 PM
    • Marked as answer by Alexander.Burton Wednesday, January 5, 2011 7:28 PM
    • Unmarked as answer by Sachin Vashishth Tuesday, November 6, 2012 12:16 PM
    Wednesday, January 5, 2011 3:15 PM
  • Sorry for misunderstanding, didn't noticed you wanted to retrieve difference between 2 different task i.e. Task A & C ,based on calendar, Formula in task level custom field won't work, And since your project template is fixed, it can be done using simple Macro, you may further extend it to suit your needs 

    Sub DateDiff()
        Dim ts As Tasks
        Dim t As Task
        Dim Startx, Finishx As Date
        Dim dur As Integer
        Set ts = ActiveProject.Tasks
        
        Set t = ts(5) 'Replace 5 with task index
        Startx = t.Start
        Set t = ts(7) 'Replace 7 with task index
        Finishx = t.Finish
        dur = (Application.DateDifference(Startx, Finishx, Standard) / 480)
        MsgBox (CStr(dur) & " " & Days)
    End Sub

    Hope this helps :)

     


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com
    Wednesday, January 5, 2011 6:05 PM
  • Same mistake by me ;-)
    Wednesday, January 5, 2011 6:07 PM
  • No issue Barbara, I have already tried this function ProjDateDiff() but not able to fetch the datediff for two different tasks.

    But anyways "Macro" Suggested by Sunil Kr Singh is giving me the exact data as I was looking for.


    Sachin Vashishth MCTS
    Thursday, January 6, 2011 6:32 AM
  • Thanks Sir,

    Bingo..

    This macro is working exactly in the same way as I want. Giving me datediff excluding weekends and holidays.

    One more thing I want to clear to run this "Function", I need to add this Macro in enterprise global template and have to fire on a particular event so that this macro will run and populate the data.


    Sachin Vashishth MCTS
    Thursday, January 6, 2011 6:40 AM
  • hi Mr. Singh

    how are you

    i want the code using the access 2010 to do the seam to do the date difference excludig holidays and weekend

    thank you

    
    
    
    
    Monday, April 30, 2012 9:00 AM
  • Hello Sirhannet

    Didn't quite get what do you want with access 2010, can you elaborate a bit more and specific results you are trying to achieve that would

    help us understand better and provide resolutions or workarounds


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com

    Monday, April 30, 2012 3:57 PM