One of the things I did in an application was to build a full fledged calendar into it. I defined a table with calendar date, flag for is weekday and flag for is holiday. I have an procedure to populate the table for any year and then the users can add in the federal holidays. Using simple SQL statements you can do a lot of what you are asking in this thread. You can embellish the table by adding in columns for next and previous business days, day of the week, week number, month number and whatever else you might want. Granted it’s a bit of work to get it going, but you always then have it and the concept is reusable.
Ken From: [email protected] [mailto:[email protected]] On Behalf Of Karen Tellef Sent: Wednesday, May 06, 2015 5:10 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Previous business day function? Yes, what I needed was a business date that is "so many business days" before a certain date, and the starting date can be a Saturday or Sunday. The code I posted works so I'll stick with that for now, but I'll probably test yours! Karen -----Original Message----- From: Javier Valencia <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Wed, May 6, 2015 3:56 pm Subject: [RBASE-L] - Re: Previous business day function? Karen, The code I posted before assumed that the starting day was always a weekday and not a weekend. The following code should give you the “previous” business date regardless of day of the week: SET VAR vPWD = (IFEQ(TDWK(.vDate),'Monday',(.vDate - 3),IFEQ(TDWK(.vDate),'Sunday',(.vDate - 2),(.vDate -1) ))) For example if your starting date is Saturday, Sunday or Monday, it will always will give you the date for the previous Friday. Is this more along the lines of what you need? The code above is the “previous” business date equivalent of the next business day (DNW)…I think… Now, if you need a date that is so many “business” days before a certain date – say, what business day/date is 18 days before a certain date? - then I am sure the code above can be modified to include that as well… Javier, Javier Valencia, PE O: 913-829-0888 H: 913-397-9605 C: 913-915-3137 From: [email protected] [mailto:[email protected] <mailto:[email protected]?> ] On Behalf Of Karen Tellef Sent: Wednesday, May 06, 2015 1:26 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Previous business day function? Yes, I already have the code to determine the number of business days back (I'll copy the full code here in case anyone else finds it useful). But the DNW function is so darn handy that I was just hoping for a reverse-DNW! -- Set the number of days to go backwards SET VAR vDays = (.vRDays + .vPDays + .vSDays) -- We start with the Due Date SET VAR vBDate = .vDDate SET VAR vCount INT = 0, vLoop INT = 1 WHILE vLoop <= .vDays THEN SET VAR vTestDate = (.vBDate - 1) IF (IDWK(.vTestDate)) BETWEEN 1 AND 5 THEN SET VAR vLoop = (.vLoop + 1) ENDIF SET VAR vBDate = .vTestDate ENDWHILE Karen -----Original Message----- From: Javier Valencia <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Wed, May 6, 2015 1:15 pm Subject: [RBASE-L] - RE: Previous business day function? Karen, Yu can probably use the following workaround; SET VAR vDate DATE = .#DATE -- or whatever date you want SET VAR vPWD = (IFEQ(TDWK(.vDate),'Monday',(.vDate -3),(.vDate-1))) The function TDWK return the “Day” of the week as text. Now, if your date is on a Monday, it will return the previous Friday, otherwise it will return the previous day. Just like the DFNW it does not consider holidays, although you can create stored function with lookup table that does that for you. You can also use the function : IDWK(arg) Which will return the day of the week as an INTEGER and then instead of “Monday” you compare it to 1. Probably other ways to do it but the ones above seem to be pretty straight forward. Javier, Javier Valencia, PE O: 913-829-0888 H: 913-397-9605 C: 913-915-3137 From: [email protected] [mailto:[email protected] <mailto:[email protected]?> ] On Behalf Of Karen Tellef Sent: Wednesday, May 06, 2015 10:49 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Previous business day function? I'm happily using the DNW(.vdate) function with a loop to go out business days. But now I also need a function that goes backwards to a PREVIOUS business day. I don't see a function for that, right? So I have to do the day of the week thing? Karen
