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

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

• Wednesday, January 05, 2011 3:09 PM

Dear All,

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

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

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

Sorry, I think you will have to replace ";" by ",": ProjDateDiff( date1, date2, calendar )
• Wednesday, January 05, 2011 6:05 PM
Moderator

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 Startx, Finishx As Date
Dim dur As Integer

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

Same mistake by me ;-)
• Thursday, January 06, 2011 6:32 AM

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

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

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

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