Hi there,
I have to calculate the days between two date. But between monday to
saturday. The DIAS.LAB function calculate the total days between monday and
friday and that is my problem, I have to include saturday.
I really want to know is there another function or how can resolve this
problems. Thansk in advance

On Mon, 31 Oct 2005 09:04:21 -0800, "Gerardo"
wrote:

Hi there,
I have to calculate the days between two date. But between monday to
saturday. The DIAS.LAB function calculate the total days between monday and
friday and that is my problem, I have to include saturday.
I really want to know is there another function or how can resolve this
problems. Thansk in advance


To calculate days between dates, without taking into account weekends at all,
merely subtract one from the other:

A1: =StartDate
A2: =EndDate

Days between = EndDate-StartDate

You may need to add '1' to the total as the NetWorkdays function is INclusive
and merely subtracting does not include StartDate.

If you are looking for a function similar to NetWorkdays, but where Sunday is
the only weekend day, then:

You can use this formula from Bob Phillips (you'll have to translate it to your
language):

=SUMPRODUCT(INT((MAX(end_date,start_date)-WEEKDAY(MAX(end_date,start_date)+1
-{2;3;4;5;6;7})-MIN(end_date,start_date)+8)/7))
-SUMPRODUCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4 ;5;6;7},0))*(holidays=MI
N(end_date,start_date))*(holidays<=MAX(end_date,st art_date)))

Or you can use this UDF which should translate automatically:

To use the UDF, enter a formula in the form:

=NWD(StartDate, EndDate, [holidays])

[holidays] is an optional argument referring to a list of holiday dates not to
be included.

To enter the UDF, <alt<F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens. The UDF actually allows you to specify
your workweek as you wish, but I have entered the coding for a Sunday only
weekend. So you should not have to make any changes.

============================
Option Explicit

Function NWD(StartDate As Date, EndDate As Date, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 1, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0, _
Optional WeekendDay_4 As Integer = 0) As Long
' Sunday = 1; Monday = 2; ... Saturday = 7

'credits to Myrna

Dim i As Long
Dim Count As Long
Dim H As Variant
Dim w As Long
Dim SD As Date, ED As Date
Dim DoHolidays As Boolean
Dim NegCount As Boolean

DoHolidays = Not (Holidays Is Nothing)

SD = StartDate: ED = EndDate
If SD ED Then
SD = EndDate: ED = StartDate
NegCount = True
End If

w = Weekday(SD - 1)
For i = SD To ED
Count = Count + 1
w = (w Mod 7) + 1
Select Case w
Case WeekendDay_1, WeekendDay_2, WeekendDay_3, WeekendDay_4
Count = Count - 1
Case Else
If DoHolidays Then
If IsNumeric(Application.Match(i, Holidays, 0)) Then _
Count = Count - 1
End If
End Select
Next i
If NegCount = True Then Count = -Count
NWD = Count
End Function
==================================


--ron