|
|
Re: Excel "WORKDAY" function in Oracle PL/SQL [message #364573 is a reply to message #364572] |
Tue, 09 December 2008 02:36   |
Frank Naude
Messages: 4593 Registered: April 1998
|
Senior Member |
|
|
Excel syntax is:
WORKDAY(start_date, days, holidays)
From the Excel help (F1):
"Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed."
|
|
|
|
|
|
|
Re: Excel "WORKDAY" function in Oracle PL/SQL [message #364632 is a reply to message #364608] |
Tue, 09 December 2008 06:28   |
Frank Naude
Messages: 4593 Registered: April 1998
|
Senior Member |
|
|
This should do the trick:
CREATE OR REPLACE FUNCTION workdays(start_date DATE, days NUMBER)
RETURN DATE
IS
i NUMBER := 0;
tot NUMBER := 0;
BEGIN
IF days = 0 THEN
RETURN start_date;
END IF;
WHILE true LOOP
IF days > 0 THEN
i := i + 1;
ELSE
i := i - 1;
END IF;
IF TO_CHAR(start_date + i, 'FMDAY') NOT IN ('SATURDAY', 'SUNDAY') THEN
tot := tot + 1;
END IF;
IF abs(tot) = abs(days) THEN
exit;
END IF;
END LOOP;
RETURN start_date + i;
END workdays;
/
|
|
|
Re: Excel "WORKDAY" function in Oracle PL/SQL [message #364633 is a reply to message #364632] |
Tue, 09 December 2008 06:36  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
For dates in the future, you can do it in a single inline calculation (Shown here in SQL):with src as (select sysdate dte
,level days
from dual
connect by level <= 15)
select dte + days + (floor(days/5)*2)
+ CASE to_number(to_char(dte + days + (floor(days/5)*2),'D','nls_date_language=english'))
WHEN 6 THEN 2
WHEN 7 THEN 1
ELSE 0
END work_day
,dte
,days
from src;
|
|
|