work days between two dates without cycling through dates

Will_Rickards (Will Rickards) — Thu, 13 Oct 2005 23:24:13 GMT

I was thinking about how to optimize figuring out the work days between two dates and came up with this function. It doesn't take into account holidays. You would have to take out the workdays for holidays from the number if you want to take into account holidays, but that should be easy enough.

4GL Version

 

# workdays

# returns the number of working days between two dates

FUNCTION workdays( dt_begin, dt_end )



DEFINE

   dt_begin             DATE,

   dt_end               DATE,

   dt_first_sunday      DATE,

   dt_last_saturday     DATE,

   int_workdays         INTEGER



   # get first sunday

   LET dt_first_sunday = dt_begin + ((7 - WEEKDAY(dt_begin)) MOD 7)



   # get last saturday

   LET dt_last_saturday = dt_end + ((-1 * (WEEKDAY(dt_end) + 1)) MOD 7)



   # get work weeks between first sunday and last saturday

   LET int_workdays = (((dt_last_saturday - dt_first_sunday) + 1) / 7) * 5

   

   # if first sunday is not begin date

   IF dt_first_sunday <> dt_begin THEN



      # assume first sunday is after begin date

      # add workdays from begin date to first sunday

      LET int_workdays = int_workdays + (6 - WEEKDAY(dt_begin))



   END IF



   # if last saturday is not end date

   IF dt_last_saturday <> dt_end THEN



      # assume last saturday is before end date

      # add workdays from last saturday to end date

      LET int_workdays = int_workdays + WEEKDAY(dt_end)  

  

   END IF



   # return working days

   RETURN int_workdays



END FUNCTION

VBA Version



' WorkDays

' returns the number of working days between two dates

Public Function WorkDays(ByVal dtBegin As Date, ByVal dtEnd As Date) As Long



   Dim dtFirstSunday As Date

   Dim dtLastSaturday As Date

   Dim lngWorkDays As Long



   ' get first sunday in range

   dtFirstSunday = dtBegin + ((8 - Weekday(dtBegin)) Mod 7)



   ' get last saturday in range

   dtLastSaturday = dtEnd - (Weekday(dtEnd) Mod 7)



   ' get work days between first sunday and last saturday

   lngWorkDays = (((dtLastSaturday - dtFirstSunday) + 1) / 7) * 5



   ' if first sunday is not begin date

   If dtFirstSunday <> dtBegin Then



      ' assume first sunday is after begin date

      ' add workdays from begin date to first sunday

      lngWorkDays = lngWorkDays + (7 - Weekday(dtBegin))



   End If



   ' if last saturday is not end date

   If dtLastSaturday <> dtEnd Then



      ' assume last saturday is before end date

      ' add workdays from last saturday to end date

      lngWorkDays = lngWorkDays + (Weekday(dtEnd) - 1)



   End If



   ' return working days

   WorkDays = lngWorkDays



End Function

replace in Informix 4GL

Will_Rickards (Will Rickards) — Fri, 23 Sep 2005 01:27:07 GMT

Replace algorithm in Informix 4GL so I can find it later.



DEFINE

   str_hitext           CHAR(130),

   str_text             CHAR(130),

   str_escape           CHAR(1)



   LET str_escape = ASCII 27



FUNCTION replace_algorithm()



DEFINE

   int_temp2            INTEGER,

   int_hipos            INTEGER,

   int_hipos_old        INTEGER,

   int_pos              INTEGER



   # remove highlight characters from text

   LET int_temp2 = LENGTH(str_hitext)

   LET int_pos = 1

   LET str_text = " "

  

   # cycle through highlight string looking for highlight strings

   LET int_hipos = 1

   LET int_hipos_old = 1

   WHILE int_hipos <= int_temp2 - 7



      # if we found an escape character (start of highlight sequence)

      IF str_hitext[int_hipos, int_hipos] = str_escape THEN



         # if a highlight sequence

         IF str_hitext[int_hipos + 1, int_hipos + 7] = "[32703m" OR str_hitext[int_hipos + 1, int_hipos + 7] = "[32723m" THEN

 

            # if there is previous data to copy over

            IF int_hipos - 1 >= int_hipos_old THEN



               # copy data over

               LET str_text[int_pos, int_pos + int_hipos - 1 - int_hipos_old] = str_hitext[int_hipos_old, int_hipos - 1]

               LET int_pos = int_pos + int_hipos - int_hipos_old



            END IF



            # move source positions - don't copy this over

            LET int_hipos = int_hipos + 8

            LET int_hipos_old = int_hipos



         # if not a highlight sequence

         ELSE



            # move source position

            LET int_hipos = int_hipos + 1



         END IF



      # if we didn't find an escape character

      ELSE



         # move source position

         LET int_hipos = int_hipos + 1



      END IF



   END WHILE



   # if there is previous data to copy over

   IF int_temp2 >= int_hipos_old THEN



      # copy data over

      LET str_text[int_pos, int_pos + int_temp2 - int_hipos_old] = str_hitext[int_hipos_old, int_temp2]



   END IF 



END FUNCTION

DZone Snippets: 4gl code

work days between two dates without cycling through dates

replace in Informix 4GL