I am trying to calculate the networkdays between 2 dates just as in excel we do with networkdays formulae.
I tried using pretty much all formuales on forums but not able to get the correct results.
Can any one guide me through this.
Thanks,
–H
hourigan (BOB member since 2007-04-06)
Hi,
I havent came across the Networkdays in the Excel, but can you please elaborate network days?
Omkar Paranjpe 
(BOB member since 2006-02-13)
It calculates only working days between 2 dates including start date and end date as in the attachment.
Thanks,
–H
hourigan (BOB member since 2007-04-06)
There is no “networkdays” functionality in BOE. Depending upon the fidelity required in the output, you could consider taking any result and multiplying it by .69 to get a derivative answer. (.69 comes by way of calculating that there are 21 “working days” in a typical month and there are 12 months in a year divided by 365 (21*12)/365.)
Just my humble thoughts on simulating “networkdays” in a WebI report or in building an object in Designer…
Thanks,
John
jsanzone (BOB member since 2006-09-12)
Could you please eloborate in detail.
Thanks,
–h
hourigan (BOB member since 2007-04-06)
Elaboration in detail:
When working in the universe, you can create an object and in the select phase perform: ( - ) * .69
(assume that is the date that a trouble-ticket was closed out and is the date that a trouble-ticket was initiated. Suppose represents Mar 5, 2008 and represents Mar 10, 2008. Performing this capabilty will yield 3.45 (or rounded to 3 days). In SQL Server, here is an example:
select datediff(day,cast('3/5/2008' as datetime),cast('3/10/2008' as datetime))*.69If you want to do this in WebI, then you’ll have to create a local variable and use the DaysBetween() function:
=(DaysBetween([start_date_date];CurrentDate()))*0.69Thanks,
John
jsanzone (BOB member since 2006-09-12)
Thank you very much for the reply. But this is not working for all dates.
I am usin this in web I. The results are as in the attachment.
Any more ideas.
Regards,
–H
hourigan (BOB member since 2007-04-06)
H–
I’m assuming that the latest screen shot you provide is the implementation of my .69 factor and the resultant report, and comparing that to your first posting and screenshot from Excel, and the two are not matching up. You’re right, this will not match up at the granular level of detail. The suggestion I had for factoring .69 was to support reporting over long periods of time and many rows of data, then the orders of magnitude will equate, but not at the lowest level of details – just as you are experiencing here.
My suggestions:
I’m glad that you’ve brought this issue up being that I now know of two commercial application software packages that perform the “networkdays” function – Excel and SAS (via proc CPM)
Thanks,
John
jsanzone (BOB member since 2006-09-12)
I have seen this done with a very clever full client formulae,not written by me I might add 
, If I can find it I will post the link.
My problem is, what is your definition of a working day, excluding public holidays, which change every year, or not excluding them?
The formule, mentioned above, could do everything but public holidays, but obviously, they are not mathmatically calculable .
The best thing you can do is create a DB calendar table for this sort of thing, manual intervention will be required each year to add the addition of public holidays, if that is what is required.
A good calendar table is one of the most powerful tables in any database IMHO.
Mak 1 
(BOB member since 2005-01-06)
I am not excluding any public holidays.I am only excluding weekends.And the formuale does not work for the dates scenario as in my previous screen shot.
Please let me know if you find the exact formuale to do this.
And how can we create a DB calendar table.Can you explain this in detail?
Thanks,.
–H
hourigan (BOB member since 2007-04-06)
To be fair JS stated that his way was not an exact science but more of an average.
Have a look at this, I haven’t used it, but I think it will do what you require:-
https://bobj-board.org/t/73436
As stated before you are best creating a calendar table, there are plenty of examples on the web - Google is your friend 
.
Depending on the different date attributes required by your business will obvoiusly drive the code requirement. Also dependent will be the start from and to date, how far do you need to look back and how far in future do you wish to look forward?
We have things like, Calendar Date, Weekend Date, Quarter, Year, Week Number in Year, Day Number of Year, Day of Week(Char), Month Name(Char) Epoch Days, Months, Weeks, Years Financial Year, Quarter, Month…e.t.c.
Get one set up, you won’t regret it… 8) .
Mak 1 
(BOB member since 2005-01-06)
I had a similar issue. The way I resolved it is by calculating it upfront and store it in the database. But I’ll sure be watching this topic in case there is another solution.
Just thinking, maybe it’s possible to store a workingday flag in your time dimension (having weekdays=1, weekends=0) and then sum this everytime.
meutte 
(BOB member since 2007-12-06)
Hi Meutte, there is no better solution than having an Intelligent calendar, I assure you… 8). Just make sure it can also join where an event has not happened yet if using a star schema.
FYI - We have a weekend indicator flag in ours, as well as many more date variants than are listed above and are planning to have short months and dates of month, numerically prefixed for sorting purposes e.g.
Short Months of Year - 01-Jan, 02-Feb, 03-Mar e.t.c
Short days of Month - 1-Jan, 2-Jan, 3-Jan, 4-Jan, e.t.c.
These are for using as scales for small webi dashboard type graphs, where size of fonts are an issue.
Of course you can do these in a universe or as a report level formulae, but they are better ina calendar IMHO.
You can then alias your calendar in the universe or, even better, have role playing calendar dimensions / views as this will allow you to re-platform DB or BI tool easily .
Mak 1 (BOB member since 2005-01-06)
You can give this a go, took some time to test but is 99% robust howver does not take into account holidays just excludes working days
=Floor((DaysBetween([start_date];[end_date]))-Truncate((DayNumberOfWeek([start_date])+DaysBetween([start_date];[end_date])) /7 ;0)*2)+1
Depending on timezones etc you may need to apply a relative date to the [start_date] and [end_date]
At the risk of teaching people to suck eggs please make sure you relace start_date and end_date with your own variables or ven easier create a variable for each called start_date and end_date then create a third variable and just past the formula and it will just work
iwood (BOB member since 2009-02-11)
passed this ticket and thought I put my solution.
My requirements where a bit more then just excluding the weekenddays.
I exclude here weekends (which are country specific 
)
exclude public holidays (stored in a table) if they are in a specific region
and then something else (called weeklyclosingdays)
i created an oracle function, providing all parameters that are not static and it returns the sum of weekends, public holidays,…
Then I substract this in a view from the date1-Date2 so I get a kind of corrected delay, or nr of workingdays if you please.
hope it helps,
I tried a lot of other things before this, but this is really performant and allows me to take into account a lot more then just the weekends.
ciao, Meutte
CREATE OR REPLACE FUNCTION FCT_CalcForeseenDelay (
T_FirstDate DATE,
T_LastDate DATE,
T_countrycode INTEGER,
T_weeklyclosingdays varchar2,
T_Region number,
T_CheckWeekend number --0= don't check for a 'weekend'day, 1 check for weekenddays
)
RETURN INTEGER
IS
V_Firstdate DATE;
V_Foreseen NUMBER := 0;
V_dayofweek varchar2(2);
E_NotFilled EXCEPTION;
CURSOR c_holiday
IS
SELECT holiday_date, region
FROM dwh.d_holidays
WHERE countryansicode = T_countrycode;
BEGIN
V_Firstdate := TRUNC (T_Firstdate+1);
If T_LastDate is null or T_FirstDate is null then RAISE E_notfilled ; end if;
WHILE V_Firstdate <= T_LastDate
LOOP
select DECODE (TO_CHAR (V_Firstdate, 'dy'),
'mon', SUBSTR (T_weeklyclosingdays, 1, 1),
'tue', SUBSTR (T_weeklyclosingdays, 2, 1),
'wed', SUBSTR (T_weeklyclosingdays, 3, 1),
'thu', SUBSTR (T_weeklyclosingdays, 4, 1),
'fri', SUBSTR (T_weeklyclosingdays, 5, 1),
'sat', SUBSTR (T_weeklyclosingdays, 6, 1),
'sun', SUBSTR (T_weeklyclosingdays, 7, 1),
999
)into v_dayofweek from dual ;
if (T_CheckWeekend = 1 and T_countrycode not in (31,34) and TO_CHAR (V_Firstdate, 'dy') IN ('sun', 'mon') )
or (T_CheckWeekend = 1 and T_countrycode in (34) and TO_CHAR (V_Firstdate, 'dy') IN ('sun', 'sat')and V_Firstdate<>trunc(T_LastDate))
or (T_CheckWeekend = 1 and T_countrycode in (31) and TO_CHAR (V_Firstdate, 'dy') IN ('sun', 'sat'))
then V_Foreseen := V_Foreseen + 1; -- increment holiday counter
else
if v_dayofweek = 'C'
THEN V_Foreseen := V_Foreseen + 1;
else
FOR recholiday IN c_holiday
LOOP
IF V_Firstdate = recholiday.holiday_date and
(case when T_countrycode= 34 and T_region = 999 then recholiday.region else T_region end) =case when T_region<>999 and recholiday.region=999 then T_Region else recholiday.region end
THEN
V_Foreseen := V_Foreseen + 1; -- increment holiday counter
END IF;
END LOOP;
end if;
end if;
V_Firstdate := V_Firstdate + 1;
END LOOP;
RETURN V_Foreseen;
EXCEPTION
when E_NotFilled then Return 999;
END ;
/meutte (BOB member since 2007-12-06)