Normal Days to business days

[Philipp Oberleitner]

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

 

[Don Guillett]

Try HELP for WORKDAY or NETWORKDAYS

 

[Norman Harker]

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.

 

[Philipp Oberleitner]

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

 

[Norman Harker]

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&":"&EndDate))))


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.

 

[Daniel.M]

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.