Normal Days to business days
 

Can i somehow calculate with excel the normal days to business days. f.e if
i have a Now() - 23.4.2004 i want to have the number of days lying in this
period without Sundays/Saturdays.

Thanks alot

Normal Days to business days
 

Try HELP for WORKDAY or NETWORKDAYS

--
Don Guillett
SalesAid Software

"Philipp Oberleitner" wrote in message
...
Can i somehow calculate with excel the normal days to business days. f.e
if
i have a Now() - 23.4.2004 i want to have the number of days lying in this
period without Sundays/Saturdays.

Thanks alot

Normal Days to business days
 

Hi Philipp!

Where earliest date is in A1

=NETWORKDAYS(A1,NOW(),HolidaysRange)

NETWORKDAYS is an Analysis ToolPak function so Analysis ToolPak needs
to be installed and selected as an Addin.

The HolidaysRange argument is optional but will exclude dates in
HolidaysRange that are between your dates.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Normal Days to business days
 

Can i embedd this in a macro and is there a possibility to do it without an
addin ?


"Norman Harker" wrote in message
...
Hi Philipp!

Where earliest date is in A1

=NETWORKDAYS(A1,NOW(),HolidaysRange)

NETWORKDAYS is an Analysis ToolPak function so Analysis ToolPak needs
to be installed and selected as an Addin.

The HolidaysRange argument is optional but will exclude dates in
HolidaysRange that are between your dates.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Normal Days to business days
 

Hi Philipp!

You can't use it in VBA unless you have selected Analysis ToolPak -
VBA

Then you need to either refer to that Addin in your code or reference
the Addin.

But this can cause problems with running your workbook on another
setup, which is one reason why we tend to avoid Analysis ToolPak
functions.

One workbook formula for avoiding Analysis ToolPak is:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(BeginDate&":"&EndDate)),2)<= 5),1-COUNTIF(Holidays,ROW(INDIRECT(BeginDate&":"&EndDat e))))


Now how you do that in VBA, when I'm about to go to bed.... I'm sure
someone on this group has done this before and will post a ready made.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Normal Days to business days
 

Hi Philipp,

You can build your own little VBA function and use it either in your spreadsheet
directly or invoked in a macro.

For an example in a macro:
nWkDays = NWD(DateSerial(2004,4,23), Date)

Function NWD(ByVal Start As Date, ByVal Finish As Date) As Long
Dim Tot As Long, nSat As Long, nSun As Long

Tot = 1 + Finish - Start
nSat = Int((Finish - Weekday(Finish - 6) - Start + 8) / 7)
nSun = Int((Finish - Weekday(Finish) - Start + 8) / 7)

NWD = Tot - nSat - nSun

End Function

Advise if you need the Holidays parameter.

Regards,

Daniel M.

"Philipp Oberleitner" wrote in message
...
Can i embedd this in a macro and is there a possibility to do it without an
addin ?