Home » SQL & PL/SQL » SQL & PL/SQL » Excel "WORKDAY" function in Oracle PL/SQL (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
Excel "WORKDAY" function in Oracle PL/SQL [message #364570] Tue, 09 December 2008 02:30 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi guys,

Do any of you have the pl/sql code for Excel equivalent of the "WORKDAY" function ?


Re: Excel "WORKDAY" function in Oracle PL/SQL [message #364572 is a reply to message #364570] Tue, 09 December 2008 02:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What does the Excel WORKDAY function do?
Re: Excel "WORKDAY" function in Oracle PL/SQL [message #364573 is a reply to message #364572] Tue, 09 December 2008 02:36 Go to previous messageGo to next message
Frank Naude
Messages: 4579
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 #364577 is a reply to message #364573] Tue, 09 December 2008 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, there is no built-in function, the thread Counting the number of business days between 2 date in AskTom should help.

Regards
Michel
Re: Excel "WORKDAY" function in Oracle PL/SQL [message #364579 is a reply to message #364572] Tue, 09 December 2008 02:45 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
ok here it is Smile

Given any date and a duration, it calculates the number of working days after (if the duration is positive) or before (if the duration is negative) the date.

Examples are attached, please check

Re: Excel "WORKDAY" function in Oracle PL/SQL [message #364580 is a reply to message #364579] Tue, 09 December 2008 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read my previous post and the associated link.

Regards
Michel
Re: Excel "WORKDAY" function in Oracle PL/SQL [message #364608 is a reply to message #364580] Tue, 09 December 2008 04:32 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
hey michel thanks Smile
Re: Excel "WORKDAY" function in Oracle PL/SQL [message #364632 is a reply to message #364608] Tue, 09 December 2008 06:28 Go to previous messageGo to next message
Frank Naude
Messages: 4579
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 Go to previous message
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;
Previous Topic: compnent "gettimeband" must be declared
Next Topic: update time
Goto Forum:
  


Current Time: Fri Apr 19 10:34:12 CDT 2024