Networdays And Calendars
 

****_*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

Networdays And Calendars
 

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
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=532728

Networdays And Calendars
 

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/


--
mrice
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=532728

Networdays And Calendars
 

=IF(A22WORKDAY(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"
wrote in message
...

****_*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

Networdays And Calendars
 

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(B1WORKDAY(A1,4),B1-WORKDAY(A1,4),"")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=532728