Calculate Working Days

IF Employer = "WSS" THEN workDaysPerYear = 365.25

Earlier in the year I needed to write a routine to calculate "Working Days" for a client. Rather than reinvent the wheel (and succumbing to my lazy streak) I went out to PEG and begged help.

Richard Elvin from Franklin Templeton sent me some rather elegant code to count weekends but the balance of opinion was that I'd need to have a database table to be able to count Public Holidays / Bank Holidays /Federal Holidays (substitute your own country's nomenclature).


The weekend calculation which has been rewritten into a structured program (workDays.p)has for its main logic

=========================================================================

/* If from date after to date swap them
 * over, and return negative answer...
 */
IF pdtDateTo < pdtDateFrom
THEN ASSIGN gdtTemp     = pdtDateFrom
            pdtDateFrom = pdtDateTo
            pdtDateTo   = gdtTemp
            giSign      = -1.


ASSIGN giDays = pdtDateTo - pdtDateFrom.

/* Deduct Weekends...
 * Date - WEEKDAY( Date ) gives
 * the Saturday Preceding Date
 */
ASSIGN giWeekEnds = TRUNCATE( ( ( pdtDateTo - WEEKDAY( pdtDateTo ) )
   - (pdtDateFrom - WEEKDAY( pdtDateFrom ) ) ) / 7, 0 ).

IF WEEKDAY( pdtDateTo ) = 7 THEN giDays = giDays - 1.
IF WEEKDAY( pdtDateFrom ) = 1 THEN giDays = giDays - 1.

ASSIGN giWorkDays = giDays - ( giWeekEnds * 2 ).

=========================================================================

Pretty neat huh? This, of course will work anywhere that has Saturdays and Sundays as "days off". If your part of the world doesn't get Saturdays and Sundays off, I apologise but at least you're beginning to see what it's like working at White Star.

It turned out that the company that I was writing the code for DID have a database table for holidays so all was happy. And then I got to thinking...

Well, you know how it is, you can either fill in your tax returns and get your paperwork up to date or you can play with the language. Of course, being a good citizen, I "put off until tomorrow" the paperwork and started researching holiday calculations (wonderful place that wikipedia!). Mostly pretty straight forward as it turns out. First Monday in May, Fourth Thursday in November - that kind of thing. And then I hit Easter.

If you live in the USA then who cares? But in the UK everyone gets either one or both of Good Friday and Easter Monday off.

Wikipedia gave me a choice. In the end I chose the Meeus/Jones/Butcher Gregorian algorithm because it was a straight calculation and didn't need any intermediate tables and it looks like this...


=========================================================================

DEFINE INPUT PARAMETER pYear AS INTEGER NO-UNDO.

/* First, find Easter Sunday. */

DEFINE VARIABLE iaa AS INTEGER NO-UNDO.
DEFINE VARIABLE ibb AS INTEGER NO-UNDO.
DEFINE VARIABLE icc AS INTEGER NO-UNDO.
DEFINE VARIABLE idd AS INTEGER NO-UNDO.
DEFINE VARIABLE iee AS INTEGER NO-UNDO.
DEFINE VARIABLE iff AS INTEGER NO-UNDO.
DEFINE VARIABLE igg AS INTEGER NO-UNDO.
DEFINE VARIABLE ihh AS INTEGER NO-UNDO.
DEFINE VARIABLE iii AS INTEGER NO-UNDO.
DEFINE VARIABLE ijj AS INTEGER NO-UNDO.
DEFINE VARIABLE ikk AS INTEGER NO-UNDO.
DEFINE VARIABLE ill AS INTEGER NO-UNDO.
DEFINE VARIABLE imm AS INTEGER NO-UNDO.
DEFINE VARIABLE iMonth AS INTEGER NO-UNDO.
DEFINE VARIABLE iDay AS INTEGER NO-UNDO.

ASSIGN iaa = pYear MOD 19
       ibb = TRUNCATE (pYear / 100, 0)
       icc = pYear MOD 100
       idd = TRUNCATE (ibb / 4, 0)
       iee = ibb MOD 4
       iff = TRUNCATE ( (ibb + 8) / 25, 0)
       igg = TRUNCATE ( (ibb - iff + 1) / 3, 0)
       ihh = (19 * iaa + ibb - idd - igg + 15) MOD 30
       iii = TRUNCATE (icc / 4, 0)
       ikk = icc MOD 4
       ill = (32 + 2 * iee + 2 * iii - ihh - ikk) MOD 7
       imm = TRUNCATE ((iaa + 11 * ihh + 22 * ill) / 451, 0)
       iMonth = TRUNCATE ((ihh + ill - 7 * imm + 114) / 31, 0)
       iDay = ((ihh + ill - 7 * imm + 114) MOD 31) + 1.

CREATE ttBankHoliday.
ASSIGN ttBankHoliday.dtDate = DATE (iMonth, iDay - 2, pYear).

IF piRegionCode NE 3 /* Scotland */ THEN DO:
   CREATE ttBankHoliday.
   ASSIGN ttBankHoliday.dtDate = DATE (iMonth, iDay + 1, pYear).
END.

=========================================================================

Notice the STRICT adherance to naming standards for all those integers!!

Having got England/Wales working I did some extra coding for Northern Ireland and for Scotland and then, because it's nearly 50% of the Progress community, I had a go at the good old United States of America. Hey, mostly straightforward and then I got totally beaten by "Inauguration Day". Let's see 20th January every 4th year after an election. A Monday if the 20th's a Sunday but no time off if the 20th is a Saturday - Oh and no time off unless you're a federal employee in DC or a bunch of other places...

Anyway there's three pieces of code for you on the Web Site - stubWorkDays.w ( a driver for workDays.p), workDays.p and bankHolidayTT.p which will subtract those Public / Bank / Whatever holidays. Get the zip file at http://www.wss.com/products/downloads.html

If you disagree strongly with the algorithms, drop me a line at alan@wss.com. Likewise, if you come up with any improvements. CAN the holidays of rest of the World be calculated?