Prev: slection change to make the selection go down 1 cell
Next: loop VBA request
From: Ranae on 24 Apr 2010 22:12
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)
From: ozgrid.com on 24 Apr 2010 22:19
Use the NETWORKDAY Function.


--
Regards
Dave Hawley
www.ozgrid.com
"Ranae" <Ranae(a)discussions.microsoft.com> wrote in message
news:E0B00D73-E269-4EDA-8498-ED8A4E1BB8E6(a)microsoft.com...
> 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)

From: Peter T on 25 Apr 2010 05:17
That looks like a typo (missing S), try

=NETWORKDAYS(start_date,end_date,holidays)

Regards,
Peter T

"ozgrid.com" <dave(a)ozgrid.com> wrote in message
news:B17C62DB-4CA2-4A62-8369-EB512FC57EBD(a)microsoft.com...
> Use the NETWORKDAY Function.
>
>
> --
> Regards
> Dave Hawley
> www.ozgrid.com
> "Ranae" <Ranae(a)discussions.microsoft.com> wrote in message
> news:E0B00D73-E269-4EDA-8498-ED8A4E1BB8E6(a)microsoft.com...
>> 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)
>


From: Bernd P on 25 Apr 2010 08:43
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
From: Rick Rothstein on 25 Apr 2010 13:30
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" <bplumhoff(a)gmail.com> wrote in message
news:f93a41d8-ea2f-4b16-9da9-0ae8dc8c564f(a)k36g2000yqn.googlegroups.com...
> 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

 |  Next  |  Last
Pages: 1 2 3
Prev: slection change to make the selection go down 1 cell
Next: loop VBA request