Networdays And Calendars

[dannyboy213]

NETWORDAYS AND CALENDARS
I desperately need help in creating a formula to calculate free days at the terminal. I have two different dates. First date is the day that the container became available. Second date is the day the container returned.

1.If the container became available during business day, for instance 4/10/06 (Monday), I have a total of 5 business days to return the container, this is including the day it became available. Which means the last free day is 4/14/06 (Friday). So another example is if the container became available on 4/11/06 (Tuesday), the last free day is 4/17/06 (Monday).

However, if the container became available on a weekend like 4/9/06 (Sunday), then the free days is only 4 business days. Which means that the last free day is 4/13/06 (Thursday).

So last part of the formula is that IF I went over the free days (detention days), I want the formula to count how many CALENDAR DAYS it went over, IF I did not go over the free days then leave it blank. Here is an example:

Available Date: 4/8/06 (Saturday)
Returned Date: 4/15/06 (Saturday)
Last Free Day: 4/13/06 (Thursday)
Detentions Days: 2 Days

[mrice]

I would suggest that you use a user defined formula which takes the start and end date and implements the complex logic that you require.

[mrice]

Sorry - hit the submit button to early.

A suitable sort of function is...

Function CalculateDays(StartDate As Date, EndDate As Date)
Dim N As Date
Dim UsedDays As Integer
For N = StartDate To EndDate
DayOfWeek = Application.Weekday(N)
If N = StartDate And DayOfWeek = 0 Then UsedDays = UsedDays + 1 'Day 0 is Sunday
If DayOfWeek >= 1 Or DayOfWeek <= 5 Then UsedDays = UsedDays + 1
Next N
If UsedDays > 5 Then CalculateDays = CalculateDays - 4

End Function

I may not have got the logic total correct but the principle should work OK.

Martin

http://homepage.ntlworld.com/martin.rice1/

[Bob Phillips]

=IF(A22>WORKDAY(A21,4),NETWORKDAYS(WORKDAY(A21,4),A22),"")

A21 is available date
A22 is returned date

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"dannyboy213" <[email protected]>
wrote in message
news:[email protected]...
>
> ****_*NETWORDAYS__AND_CALENDARS*_
> I desperately need help in creating a formula to calculate free days at
> the terminal. I have two different dates. First date is the day that the
> container became available. Second date is the day the container
> returned.
>
> 1.If the container became available during business day, for instance
> 4/10/06 (Monday), I have a total of 5 business days to return the
> container, this is including the day it became available. Which means
> the last free day is 4/14/06 (Friday). So another example is if the
> container became available on 4/11/06 (Tuesday), the last free day is
> 4/17/06 (Monday).
>
> However, if the container became available on a weekend like 4/9/06
> (Sunday), then the free days is only 4 business days. Which means that
> the last free day is 4/13/06 (Thursday).
>
> So last part of the formula is that *_IF__*I went over the free days
> (detention days), I want the formula to count how many
> *_CALENDAR_DAYS_* it went over, IF I did not go over the free days then
> leave it *_blank_*. Here is an example:
>
> Available Date: 4/8/06 (Saturday)
> Returned Date: 4/15/06 (Saturday)
> Last Free Day: 4/13/06 (Thursday)
> *Detentions Days: 2 Days*
>
>
> --
> dannyboy213
> ------------------------------------------------------------------------
> dannyboy213's Profile:
http://www.excelforum.com/member.php...o&userid=31032
> View this thread: http://www.excelforum.com/showthread...hreadid=532728
>

[daddylonglegs]

If you want calendar days over.....

With available date in A1 and returned date in B1

either

=B1-WORKDAY(A1,4)

custom format cell as 0;;

or without formatting

=IF(B1>WORKDAY(A1,4),B1-WORKDAY(A1,4),"")