Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Diff in business days | Excel Discussion (Misc queries) | |||
Calculating 10 Business Days from the Last Business Day of the Mon | Excel Worksheet Functions | |||
UK Business Days Formula | Excel Worksheet Functions | |||
Business Days Only | Excel Discussion (Misc queries) | |||
How many business days were in 2005? | Excel Worksheet Functions |