Sign in
 
HomeOnline20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
Custom Field: Tasks date difference excluding "Holidays and Weekend etc."

Answered Custom Field: Tasks date difference excluding "Holidays and Weekend etc."

  • Wednesday, January 05, 2011 3:09 PM
     
     
    Sign In to Vote
    0

    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
     

All Replies

  • Wednesday, January 05, 2011 3:14 PM
    Moderator
     
     
    Sign In to Vote
    0

    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 05, 2011 3:15 PM
    Moderator
     
     Proposed Answer
    Sign In to Vote
    0
    Sorry, I think you will have to replace ";" by ",": ProjDateDiff( date1, date2, calendar )
     
  • Wednesday, January 05, 2011 6:05 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    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 05, 2011 6:07 PM
    Moderator
     
     
    Sign In to Vote
    0
    Same mistake by me ;-)
     
  • Thursday, January 06, 2011 6:32 AM
     
     
    Sign In to Vote
    0

    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 06, 2011 6:40 AM
     
     
    Sign In to Vote
    0

    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
     
  • Monday, April 30, 2012 9:00 AM
     
     
    Sign In to Vote
    0

    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 3:57 PM
    Moderator
     
     
    Sign In to Vote
    0

    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