hi,
Can someone please help me with a formula to calculate number of days,
excluding weekend days?

A1 B1 C1
Start date End date Total days (excluding weekend)

Use the NETWORKDAY Function.


--
Regards
Dave Hawley
www.ozgrid.com
"Ranae" wrote in message
...
hi,
Can someone please help me with a formula to calculate number of days,
excluding weekend days?

A1 B1 C1
Start date End date Total days (excluding weekend)

That looks like a typo (missing S), try

=NETWORKDAYS(start_date,end_date,holidays)

Regards,
Peter T

"ozgrid.com" wrote in message
...
Use the NETWORKDAY Function.


--
Regards
Dave Hawley
www.ozgrid.com
"Ranae" wrote in message
...
hi,
Can someone please help me with a formula to calculate number of days,
excluding weekend days?

A1 B1 C1
Start date End date Total days (excluding weekend)

Hello,

If you do no need to take into account holidays, I suggest to use the
4th formula shown at
http://sulprobil.com/html/date_formulas.html

Regards,
Bernd

Bernd,

I thought you might be interested in seeing this short one-liner alternative
to your "cwd" function (which requires your "min" function to operate); it
is fully self-contained and, as such, relies only on built-in VB
functions...

Function CWD(D1 As Date, D2 As Date) As Long
CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - _
(Weekday(D2) < 7) + (Weekday(D1) = 1) + (Weekday(D1, 2) < 6)
End Function

Note that if you remove the last addend, that is the "+(Weekday(d1, 2)<6)"
logical expression, then the function will return the same results as
Excel's NETWORKDAYS function. If we provide the function with an Optional
parameter, we can make it return either result (yours or NETWORKDAYS's) like
so...

Function CWD(D1 As Date, D2 As Date, Optional NWD As Boolean) As Long
CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - _
(Weekday(D2) < 7) + (Weekday(D1) = 1) - NWD * (Weekday(D1, 2) < 6)
End Function

The default for the NWD (short for NetWorkDays by the way) parameter is
False, meaning it returns the same results as does your "cwd" function...
pass True in for the NWD parameter and the function returns the same values
as Excel's NETWORKDAYS function.

--
Rick (MVP - Excel)



"Bernd P" wrote in message
...
Hello,

If you do no need to take into account holidays, I suggest to use the
4th formula shown at
http://sulprobil.com/html/date_formulas.html

Regards,
Bernd

Hello Rick,

Thanks, I like that one! I put your cute and shorter version on my
site as well (same page as above).

Your version with the optional parameter NWD is not working as
expected, though:
It returns for 23/08/1933 - 14/10/1933 (NWD is False) 38 days and not
37 as the other versions do, for example.

Regards,
Bernd

On 25 Apr., 19:30, "Rick Rothstein"
wrote:
Bernd,

I thought you might be interested in seeing this short one-liner alternative
to your "cwd" function (which requires your "min" function to operate); it
is fully self-contained and, as such, relies only on built-in VB
functions...

Function CWD(D1 As Date, D2 As Date) As Long
* CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - _
* * * * (Weekday(D2) < 7) + (Weekday(D1) = 1) + (Weekday(D1, 2) < 6)
End Function

Note that if you remove the last addend, that is the "+(Weekday(d1, 2)<6)"
logical expression, then the function will return the same results as
Excel's NETWORKDAYS function. If we provide the function with an Optional
parameter, we can make it return either result (yours or NETWORKDAYS's) like
so...

Function CWD(D1 As Date, D2 As Date, Optional NWD As Boolean) As Long
* CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - _
* * * * (Weekday(D2) < 7) + (Weekday(D1) = 1) - NWD * (Weekday(D1, 2) < 6)
End Function

The default for the NWD (short for NetWorkDays by the way) parameter is
False, meaning it returns the same results as does your "cwd" function...
pass True in for the NWD parameter and the function returns the same values
as Excel's NETWORKDAYS function.

--
Rick (MVP - Excel)

"Bernd P" wrote in message

...

Hello,

If you do no need to take into account holidays, I suggest to use the
4th formula shown at
http://sulprobil.com/html/date_formulas.html

Regards,
Bernd

Sorry, it looks like I implemented the NWD parameter backwards. This should
produce the correct results for the NWD parameter equaling True and False...

Function CWD(D1 As Date, D2 As Date, Optional NWD As Boolean) As Long
CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - (Weekday(D2) _
< 7) + (Weekday(D1) = 1) - (Not NWD) * (Weekday(D1, 2) < 6)
End Function

And here it is with the renamed parameters to enforce the requirement that
D1 must be the earlier date and D2 the later one...

Function CWD(StartDate As Date, EndDate As Date, _
Optional NWD As Boolean) As Long
CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, _
EndDate) * 2 - (Weekday(EndDate) < 7) + (Weekday(StartDate) = 1) _
- (Not NWD) * (Weekday(StartDate, 2) < 6)
End Function

--
Rick (MVP - Excel)



"Bernd P" wrote in message
...
Hello Rick,

Thanks, I like that one! I put your cute and shorter version on my
site as well (same page as above).

Your version with the optional parameter NWD is not working as
expected, though:
It returns for 23/08/1933 - 14/10/1933 (NWD is False) 38 days and not
37 as the other versions do, for example.

Regards,
Bernd

On 25 Apr., 19:30, "Rick Rothstein"
wrote:
Bernd,

I thought you might be interested in seeing this short one-liner
alternative
to your "cwd" function (which requires your "min" function to operate);
it
is fully self-contained and, as such, relies only on built-in VB
functions...

Function CWD(D1 As Date, D2 As Date) As Long
CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - _
(Weekday(D2) < 7) + (Weekday(D1) = 1) + (Weekday(D1, 2) < 6)
End Function

Note that if you remove the last addend, that is the "+(Weekday(d1,
2)<6)"
logical expression, then the function will return the same results as
Excel's NETWORKDAYS function. If we provide the function with an Optional
parameter, we can make it return either result (yours or NETWORKDAYS's)
like
so...

Function CWD(D1 As Date, D2 As Date, Optional NWD As Boolean) As Long
CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - _
(Weekday(D2) < 7) + (Weekday(D1) = 1) - NWD * (Weekday(D1, 2) <
6)
End Function

The default for the NWD (short for NetWorkDays by the way) parameter is
False, meaning it returns the same results as does your "cwd" function...
pass True in for the NWD parameter and the function returns the same
values
as Excel's NETWORKDAYS function.

--
Rick (MVP - Excel)

"Bernd P" wrote in message

...

Hello,

If you do no need to take into account holidays, I suggest to use the
4th formula shown at
http://sulprobil.com/html/date_formulas.html

Regards,
Bernd

Hello Rick,

Great. Tested and published as well. No need anymore for my cwd
version. Thanks.

Regards,
Bernd

On 25 Apr., 21:15, "Rick Rothstein"
wrote:
Sorry, it looks like I implemented the NWD parameter backwards. This should
produce the correct results for the NWD parameter equaling True and False....

Function CWD(D1 As Date, D2 As Date, Optional NWD As Boolean) As Long
* CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - (Weekday(D2) _
* * * * * * *< 7) + (Weekday(D1) = 1) - (Not NWD) * (Weekday(D1, 2) < 6)
End Function

And here it is with the renamed parameters to enforce the requirement that
D1 must be the earlier date and D2 the later one...

Function CWD(StartDate As Date, EndDate As Date, _
* * * * * * *Optional NWD As Boolean) As Long
* CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, _
* * * * EndDate) * 2 - (Weekday(EndDate) < 7) + (Weekday(StartDate) = 1) _
* * * * - (Not NWD) * (Weekday(StartDate, 2) < 6)
End Function

--
Rick (MVP - Excel)

It should be noted that in my function, D1 must be the earlier date and D2
the later one (the DateDiff function requires this). Perhaps changing the
parameter names would help enforce this requirement...

Function CWD(StartDate As Date, EndDate As Date, _
Optional NWD As Boolean) As Long
CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, _
EndDate) * 2 - (Weekday(EndDate) < 7) + (Weekday(StartDate) = 1) _
- NWD * (Weekday(StartDate, 2) < 6)
End Function

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Bernd,

I thought you might be interested in seeing this short one-liner
alternative to your "cwd" function (which requires your "min" function to
operate); it is fully self-contained and, as such, relies only on built-in
VB functions...

Function CWD(D1 As Date, D2 As Date) As Long
CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - _
(Weekday(D2) < 7) + (Weekday(D1) = 1) + (Weekday(D1, 2) < 6)
End Function

Note that if you remove the last addend, that is the "+(Weekday(d1, 2)<6)"
logical expression, then the function will return the same results as
Excel's NETWORKDAYS function. If we provide the function with an Optional
parameter, we can make it return either result (yours or NETWORKDAYS's)
like so...

Function CWD(D1 As Date, D2 As Date, Optional NWD As Boolean) As Long
CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - _
(Weekday(D2) < 7) + (Weekday(D1) = 1) - NWD * (Weekday(D1, 2) < 6)
End Function

The default for the NWD (short for NetWorkDays by the way) parameter is
False, meaning it returns the same results as does your "cwd" function...
pass True in for the NWD parameter and the function returns the same
values as Excel's NETWORKDAYS function.

--
Rick (MVP - Excel)



"Bernd P" wrote in message
...
Hello,

If you do no need to take into account holidays, I suggest to use the
4th formula shown at
http://sulprobil.com/html/date_formulas.html

Regards,
Bernd