Math Problem..

[mission2java_78]

Big Math Problem..Only guru's please .

Here's my problem and it is very difficult to explain so bare with me.
I have interfaced with MS Project which uses a 5 day calendar (Mon-Fri)
with 8 hours a day for each day. Now in access I need to calculate the duration
and send this value to Project. Big problem though...in access Im using a 7
day calendar..so the best thing is to take an example:

User in Access enters start date of Sept 6, 2002 and end date as
Sept 16 2002. So I do the following:

I figure out the number of days: Total Days = (16-6) = 10 (end-start) fine and dandy
we also assume the end date is not counted as a full day..meaning it should be
completed on end date. So we get 10.

Now because we are dealing with MS Project calendar and how it does not consider
weekends I need to determine the number of Weeks as follows:
TotalWeeks = TotalDays/7 In this case 10/7 = 1

so, so far we know for sure there is at least 2 weekends. Now we look at a calendar and
notice really there is 4 weekends from the 6th to the 16th because of us falling on certain days.

The problem is I need to figure out the following:

TotalDurationSentTOProject = TotalDays - TotalWeekendDays

But the problem is I cannot figure out totalweekenddays. I know that we need something
as follows:
totalweekenddays = TotalWeeks*2 so far...+ some Modulus ?

Please help so I can get this worked out...in the end to Project from this example I should be sending
a TotalDurationSentToProject = 6

But I cannot calculate this??

 

[CJBIRKIN]

Hello

Don't know about being a guru but according to my calender there are only two weekends between sept-6-2002 and sept-16-2002

RE:

>4 weekends from the 6th to the 16th <

>Sept 6, 2002 and end date as
Sept 16 2002. So I do the following: <

Chris

PS there is a weekday function in EXCEL (should be available in access?) that returns the daytype i.e monday tuesday etc when given a date. You could iterate through the dates using the a loop and the function and count the number of saturdays and sundays and then minus that number....??? just a thought

 

[mission2java_78]

Function GetProjectDuration(ByVal PS As Date, ByVal pF As Date) As Long
'function counts working days only.
'1 and 7 = Sun, Saturday respectively

Dim daysDuration As Long
daysDuration = 0

Do Until PS = pF
If (Weekday(PS) <> 1 And Weekday(PS) <> 7) Then
daysDuration = daysDuration + 1
Else
'do nothing
End If
PS = PS + 1
Loop

GetProjectDuration = daysDuration

End Function

Thanks greg for the idea.