Sign in
Microsoft.com
 
Microsoft
 
 
 

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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    Same mistake by me ;-)
    Wednesday, January 5, 2011 6:07 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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